SQL String for union query on dynamic number of tables

#11

On Apr 27, 1:55*pm, Robin Riviere <deltacompan... (AT) gmail (DOT) com> wrote:

Quote:

On Apr 27, 1:45*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:

Hi Robin,

I can kind of see what is going on here - in a general sense. *You are
trying to perform operations in an Integrated developement environment
(Access) that would be better suited for a non integrated development
environment (.Net). *In the past I used to refer to this as enterprise
operations in a non enterprise system (Access). *But I think that
Integrated vs Non Integrated development environment is more accurate.
Anyway, it looks like you are trying to develop a sql string using the
"Union" operator.

I am perceiving that you want to write a query that would look something
like this -- general format:

select fld1, fld2, fld3, ... from tbl1 union all
select fld1, fld2, fld3, ... from tbl2 union all
select fld1, fld2, fld3, ... from tbl3 union all
..

I am guessing that the field names are all the same but that table names
is where you are having your issue. *Again, loop through the tabledef
collection to get the names of your linked tables. *YOu could even store
these table names in a local table and then loop through this table to
add the table names to your sql string:

Dim strSql As String, str1 As STring, str2 As String
Dim DB As DAO.Database, RS As DAO.RecordSet
Set DB = CurrentDB
Set RS = DB.OpenRecordset("Select tableName From LocalTbl")
strSql = ""
str2 = "Select fld1, fld2, fld3, ... From "
Do While Not RS.EOF
* str1 = RS!TableName
* RS.MoveNext
* If Not RS.EOF Then
* * strSql = strSql & str2 & str1 & " Union All "
* Else
* * strSql = strSql & str2 & str1
* End If
Loop

As for the non integrated thing, Querydefs work best in the integrated
environment. *For your purposes, I wouldn't use querydefs because your
are working with tables that aren't local to your DB. *It can be done,
but you will end up with a bunch of spaghetti code to make it work. I
say this respectfully.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich,

You summed it up perfectly. *Let me play with what you've posted, and
I'll reply back with my results.

Thanks for taking the time to help.
RR

Rich and everyone else who replied, thanks. I got it to work. I
really appreciate your help.

The only annoyance is that the union query returns a blank record as
the first record of the query. Any idea what this might be attributed
to?

RR

#12

Quote:

The only annoyance is that the union query returns a blank record asthe

first record of the query. Any idea what this might be attributed to?

RR
<

try hand writing your query. And it if is using a ton of tables then
try hand writing with the just the first three or four tables and see
what you get. If that does not cause a problem then try the next batch
of tables until you see the problem occur again. If you don't see the
problem, then the issue is in how you are programmatically generating
your sql string. If you are still using the querydefs, then that could
also be the issue.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

#13

On Apr 27, 4:13*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:

Quote:

The only annoyance is that the union query returns a blank record asthe
first record of the query. Any idea what this might be attributed to?

RR

try hand writing your query. *And it if is using a ton of tables then
try hand writing with the just the first three or four tables and see
what you get. *If that does not cause a problem then try the next batch
of tables until you see the problem occur again. *If you don't see the
problem, then the issue is in how you are programmatically generating
your sql string. *If you are still using the querydefs, then that could
also be the issue.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich,

Thanks for the idea. I ran several "hand written" union queries
across many combinations of tables and discovered that any combination
of more than one table in the query produced the extra row as the
first record of the query. However, if I only included one table
(therefore a simple select query) no extra row was returned. My
syntax was as follows:

SELECT Table1.* FROM Table1;

UNION SELECT Table2.* FROM Table2;

Any ideas?

Thanks,
RR

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQL String for union query on dynamic number of tables

comp.databases.ms-access comp.databases.ms-access

Re: SQL String for union query on dynamic number of tables - 04-27-2010 , 01:43 PM

Re: SQL String for union query on dynamic number of tables - 04-27-2010 , 03:13 PM

Re: SQL String for union query on dynamic number of tables - 04-28-2010 , 10:22 AM