Make Table or Append - many to one

[breilly192]

I have 100 or so like tables I was able to import into an Access mdb from Excel files. Now, I need to copy all of these individual tables into one - fields are the same in each table. I guess I should have imported them from files into one table, but that is beside the point.

Can a DoCmd.TransferSpreadsheet or DoCmdCopyObject efficiently copy all the data tables into one new table via a Do While loop or is a SQL statement better? I would have thought an insert or append query but didn't know how to code to vary the source tables within the mdb.

Any help would be appreciated.

 

[Zion7]

I believe a "Union Query" will join all your tables into one.
And from this query, you can create a new table, either using a "Make table" query, or using ADO or DAO.
My familiarity with SQL is not very broad, but, I believe this is the general idea.
Hope this helps! good luck!

 

[breilly192]

no joy. :-(

 

[jabz]

First Off you do need to create a Union query which will take some time but you should just be able to copy and paster a whole lot and just change the table name. In a query you need to do this:

SELECT *
FROM [Table1]

UNION SELECT *
FROM [Tabel2]

UNION SELECT *
FROM [Tabel3]
..........

Adding as many UNION SELCT's as you need.

After you have created your Union Query, you can run an insert query. The SQL code will be as follows:

INSERT INTO [Your Main Table to store ALL records]
SELECT * FROM [Your Union Query];

Then run the query and as long as the tables structures are the same this should do it. Let me know if you need any more help.