Combining multiple tables

[Guest]

Hello.

I have 300 + tables with identical fields in them. I need to combine all of
these tables into one table and add a field that identifies the records based
off of the table name.

Example:

Table1 fields:
Name
Date
Age
Height

Table2 fields:
Name
Date
Age
Height

The new table would combine these two tables (as well as the other 300) and
create a new main table:

NewTable fields:
ID (this would be Table1, Table2, Table3, etc)
Name
Date
Age
Height

Is there a way to do this with a script that captures the name of the table
and then appends to the new table or do I have to run 300 seperate append
tables?

Thanks for any help you can give...

 

[Allen Browne]

Loop through the TableDefs collection, building an Append query statement
(string) for each one, and Execute it.

You will need to skip the target table, and the hidden system tables.

 

[Guest]

Thanks Allen.

Im new to Access and not sure how to do that. Do you have an example of how
to do this?

 

[Allen Browne]

To program this kind of thing, you will need to become familiar with the DAO
object model.

Here's a summary of the model:
http://allenbrowne.com/ser-04.html

And here's an example of looping through the fields of a table:
http://allenbrowne.com/func-06.html