Union query for all tables that begin with the same characters

[bluegnu]

Hello,

I wondered if it is possible to create a union query for all tables which start with a specific word?

For instance - if I have a number of tables which are called table1, table2, table3 and they all contain the same fields, but I might have table 4, table5 etc added at a later date - is it possible to have a query which combines all of these as and when they are added?

hope that makes sense, thanks

 

[Golom]

You probably need to generate the SQL using VBA something like this DAO example. You can also use an ADOX catalog in ADO.

Dim tbl As DAO.TableDef
Dim SQL As String
For Each tbl In CurrentDB.Tabledefs
   If Left(tbl.Name,5) = "Table" Then [COLOR=black cyan]' Test for matching table names[/color]
      SQL = SQL & "Select * From [" & tbl.Name & "] UNION "
   End If
Next tbl
SQL = Left(SQL, Len(SQL) - 7)

 

[dhookom]

You would need to write code to build the SQL and apply it to a saved query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]