I'm using Access 2007 and am trying to create an SQL string in VBA
which loops through each record of a query containing all the names of
the tables I'd like included in a union query. Does anyone have a
quick snippet of code to identify the query containing the tables
names and then loop through each record pulling the table names from
the "tbl_Name" field?

Thanks,
RR

Here is a quick review on union queries: You have to have the same
number of columns with the same data types for each union

select fld1, fld2, fld3 from tblA union all
select fld1, fld2, fld3 from tblB union all
select fld1, fld2, fld3 from tblC union all
...

The columns don't actually have to be the same name, just the same
number and same data types.

But if you have several tables that are basically all the same then this
suggests a redundancy in your system. An RDBMS -- whether file based
(Access) or server based (Sql Server) -- is all about eliminating
redundancy. IF this is your case, you may want to retool your system.
Just a thought. Union queries are more for like if you wanted to pull
data from tables in different databases into one query.

Rich

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

On Apr 27, 11:23*am, Rich P <rpng... (AT) aol (DOT) com> wrote:
Rich,

Thanks for the feedback. The union query I have in mind will combine
a variable number of linked tables; all of which are structured
identically. I have created a query which lists the table names of
all the linked tables. My objective is to create procedure which
loops through each record in the query, capturing the table names and
adding those names to the union query string. Does that make sense?

Thanks,
RR

Robin Riviere wrote:
This identifies the fields (first in this ex) in a query
Dim q As QueryDef
Set q = CurrentDb.QueryDefs("Query1")
MsgBox q.Fields(0).Name

You could get the SQL statement
q.SQL
and then you'd need to parse it out. A query might not contain but 1
table but many with inner, left, right joins.

Here's a sub to enumerate thru to queries
Public Sub QueryList()
Dim qdf As QueryDef

For Each qdf In CurrentDb.QueryDefs
If Left(qdf.Name, 1) <> "~" Then
'do something
End If
Next qdf
MsgBox "Done"
End Sub

Hi Robin,

I guess linked tables meets the criteria of tables from different
databases. So it isn't a redundancy issue. What you can do is to loop
through the DAO.TableDef collection to get the table names. If all the
tables are linked tables you can use the TableDef.Attributes property to
distinguish this. System tables all start with 2 (or -2). Local tables
start with a 0. Anything else is a linked table (either linked to
another mdb or an ODBC connection). You can collect these table names
into an array (or another table) and then assign the names from this
array to your union query.

Here is one technique:

Dim DB As DAO.Database, tdf As DAO.TableDef
Dim arrTbl() as string, i As Integer, j As Integer
Dim str1 As String, str2 As String

i = 0
j = 0
Set DB = CurrentDb
For Each tdf In DB.TableDefs
str1 = ""
If tdf.Attributes <> 0 Then str1 = tdf.Connect
Debug.Print tdf.Attributes & " " & tdf.Name & " " &
str1

str2 = ABS(tdf.Attributes) '--absolute value
If str1 = "0" Or Left(str2, 1) <> "2" Then
i = i + 1
End If
Next
Redim arrTbl(i)
For Each tdf In DB.TableDefs
str2 = ABS(tdf.Attributes) '--absolute value
If str1 = "0" Or Left(str2, 1) <> "2" Then
arrTbl(j)j = tdf.Name
j = j + 1
End if


Next


Rich

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

Correction:

Dim DB As DAO.Database, tdf As DAO.TableDef
Dim arrTbl() as string, i As Integer, j As Integer
Dim str1 As String, str2 As String

i = 0
j = 0
Set DB = CurrentDb
For Each tdf In DB.TableDefs
str1 = ""
If tdf.Attributes <> 0 Then str1 = tdf.Connect
Debug.Print tdf.Attributes & " " & tdf.Name & " " &
str1

str2 = ABS(tdf.Attributes) '--absolute value
If str2 = "0" Or Left(str2, 1) <> "2" Then
i = i + 1
End If
Next

Redim arrTbl(i)
For Each tdf In DB.TableDefs
str2 = ABS(tdf.Attributes) '--absolute value
If str2 = "0" Or Left(str2, 1) <> "2" Then
arrTbl(j)j = tdf.Name
j = j + 1
End if
Next


Rich

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

On Apr 27, 12:35*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:
Thanks Rich. That's very helpful and more elegant than what I have
currently. However, at this point, I have created a query which
captures all the names of the tables I want to include in my union
query. The hang-up I currently have is looping through each record in
the field containing those names to add the contents of each record to
the SQL string which comprises the union query. Do you have any
advice on how to take the contents of each record and insert it into
the query string? Below is the code I have thus far...

Thanks again,
RR

Public Function MakeUnionSQL() As Boolean
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim qdf As QueryDef
Dim strSQL, strUnion As String

Set DB = CurrentDb
strSQL = ""
Application.RefreshDatabaseWindow

Set rst = dbs.OpenRecordset(qry_IDLinkedTables, dbOpenDynaset)
With rst
If Not (.EOF And .BOF) Then
.MoveFirst
Do Until .EOF
strUnion = ""
strSQL = strSQL & strUnion
strSQL = strSQL & " SELECT qry_IDLinkedTables.tblName
FROM qry_IDLinkedTables;" (This is where I'm getting screwed up...)
strUnion = " Union"
Loop
End If
.Close
End With

Set rst = Nothing
Set DB = Nothing
Set qdf = DB.CreateQueryDef("qry_TblUnion", strSQL)
DB.QueryDefs.Refresh
Application.RefreshDatabaseWindow

Set DB = Nothing
makeUnionSQL = True

End Function

The hidden system table MSysObjects can list all tables in or linked to the
MDB.

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=6));

"Robin Riviere" <deltacompany94 (AT) gmail (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 Developersdex http://www.developersdex.com ***

On Apr 27, 1:45*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:
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