Merging 100 Tables

A

Artistyc

I have a database that contains 100 tables named Data1, Data2, Data3, etc.
Each table has an identical structure. I need to get all the data in each
table into a single table. How can I do this??? I am only marginally
litterate with SQL and VBA so please bare with me if this is overly complex.

thanks in advance
 
D

Dirk Goldgar

Artistyc said:
I have a database that contains 100 tables named Data1, Data2, Data3,
etc. Each table has an identical structure. I need to get all the
data in each table into a single table. How can I do this??? I am
only marginally litterate with SQL and VBA so please bare with me if
this is overly complex.

thanks in advance
Click to expand...

Here's some model code that you could run, after suitable modification:

'----- start of example code -----
Sub AppendTables()

Dim db As DAO.Database
Dim I As Integer

Set db = CurrentDb

For I = 1 To 100
db.Execute _
"INSERT INTO [YourOutputTable] " & _
"SELECT * FROM Data" & I, _
dbFailOnError
Next I

Set db = Nothing

End Sub
'----- end of example code -----

You would replace "YourOutputTable" with the name of the table you want
to append into.

Note that the code, as written, doesn't make any provision for recording
in the output table which source table a particular record came from.
If a field were added to the output file for that purpose, the code
could be modified to supply a value for it.

This code also assumes that appending all the source tables to the
output table won't result in any conflicting keys. That would make
things somewhat more complicated.
 
Top