Merging Many Tables into ONE Table!

[piovesan]

Hi;

I have about 200 tables, all with one record each in them. The fields are all common so I have no problem doing a copy and paste of each table into one big table but this will take forever! Is there an easier, faster way to merge all these tables into one??

Thanks!

 

[RiverGuy]

There's no easy way that I can think of unless you tables have a logical naming sequence, such as Table1, Table2, Table3, etc.

If it was like that, you could run a loop and do an append statment, kind of like SELECT * FROM TABLE." & intLoop & "; " in VB. Other than that, you would have to type a bunch of different SQL statements in code, or make an append query in Access, and just remove the table and add the next one, remove it, add the next one, etc.

 

[GHolden]

This will work assuming that all tables have identical field names. If you have other tables in the db which you don't want appended modify the if statement to exclude them.
This sub appends ALL tables in the db excluding system tables and tblAppend to tblAppend. tblAppend must be set up first with identical fields to the tables to be appended.


Sub mAppend()
Dim db As Database
Dim tbl As TableDef


Set db = CurrentDb
For Each tbl In db.TableDefs
If Left(tbl.Name, 4) <> &quot;msys&quot; And tbl.Name <> &quot;tblAppend&quot; Then
db.Execute &quot; INSERT INTO tblAppend SELECT * FROM &quot; & tbl.Name
End If
Next

db.Close
Set db = Nothing

End Sub

 

[piovesan]

Thanks RiverGuy and GHolden! I already did the append query and removed the tables as I appended them but I will try your code for future use.