VBA to create a Union queries

[prav78]

Hi, I need help to write a vba code to create union queries from all the tables in my database.
All the fields are the same for all tables and the table name needs to be a field.

E.g

SELECT Table1.f1, Table1.f2, Table1.f3, "Table1" As f4TName FROM Table1
UNION SELECT Table2.f1, Table.f2, Table2.f3, "Table2" As f4TName FROM Table2
UNION SELECT TableN.f1, TableN.f2, TableN.f3, "TableN" As f4TName FROM TableN;


Thanks.

 

[alansidman]

Is this what you are looking for:

strSql = "SELECT Table1.f1, Table1.f2, Table1.f3, ""Table1"" As f4TName FROM Table1 " & vbCrLf & _
"UNION SELECT Table2.f1, Table.f2, Table2.f3, ""Table2"" As f4TName FROM Table2 " & vbCrLf & _
"UNION SELECT TableN.f1, TableN.f2, TableN.f3, ""TableN"" As f4TName FROM TableN;"

 

[prav78]

Is this what you are looking for:

strSql = "SELECT Table1.f1, Table1.f2, Table1.f3, ""Table1"" As f4TName FROM Table1 " & vbCrLf & _
"UNION SELECT Table2.f1, Table.f2, Table2.f3, ""Table2"" As f4TName FROM Table2 " & vbCrLf & _
"UNION SELECT TableN.f1, TableN.f2, TableN.f3, ""TableN"" As f4TName FROM TableN;"

Close but the code has to find all the tables and create a union query for all of them.
For example if there are 40 tables, there would be 40 union queries. Is it a For .. Next statement?

 

[xenou]

googling "msaccess vba list tables in database" gives a lot of information. This one looks pretty straightforward:
List all tables contained in the database | Database Solutions for Microsoft Access | databasedev.co.uk

I think you'll find that some tables will be "system" tables but you will see how to filter them out when you see the full list.

For what it's worth, you probably have bad table structure given this requirement. This data should be in one table, not 40 tables.

 

[tiedied61]

Here is how to loop through all the tables and grab the first 4 fields of each table... Is this what you are looking for??

Private Sub Foo()

   Dim sql As String
   Dim TableName As String
   Dim tdf As DAO.TableDef
   Dim qdf As DAO.QueryDef
   
   ' Initialize
   sql = ""
   
   ' Iterate the TableDefs collection
   For Each tdf In CurrentDb().TableDefs
      ' Get the table name
      TableName = tdf.Name
      
      ' Be sure it is not a system table
      If (Left(TableName, 4) <> "MSys") And (Left(TableName, 4) <> "USys") Then
         If Len(sql) Then
            sql = sql & " UNION "
         End If
         
         ' Surround table/field names with square brackets
         ' In case there are spaces, etc
         
         ' Grab the first 4 fields from the table
         ' (or you could hard code the names, but might not be reliable)
         sql = sql & " SELECT " & tdf.Fields(0).Name & "]"
         sql = sql & ", [" & tdf.Fields(1).Name & "]"
         sql = sql & ", [" & tdf.Fields(2).Name & "]"
         sql = sql & ", [" & tdf.Fields(3).Name & "]"
         sql = sql & ", '" & TableName & "' As f4TName"
         sql = sql & " FROM [" & TableName & "]"
         
      End If
   Next tdf
   
   ' Save the sql as as query
   Set qdf = New DAO.QueryDef
   qdf.sql = sql
   qdf.Name = "UnionQueryTest"
   CurrentDb().QueryDefs.Append qdf
   CurrentDb().QueryDefs.Refresh

End Sub