Query to Create Master Table from all Existing Tables in Database

[maggiemago3]

hello,
I need to create a Master Table in my database that would merge almost all of the existing tables. The database imports all excel files and all worksheets within those files, into seperate tables. I now need to create a master from those imported tables (however, the number of tables and names will be different everytime). The database will also contain at least 1 standard table that is always in it, named tbl_adhoc. So, effectively, I need my query to Make or Append a table from all existing tables, except the tbl_adhoc table. I also need it to add an additional column that would include the original table name reference. Although I won't know the names or quantities of the tables, I do know that they are all in the same exact format, with the same exact headers and all have 45 columns.

[ssanfu]

Not hard to do, but it will take VBA to do it.

As you import the Excel files, save the table names to a table (maybe "ImportTables")
Loop through the tables in "ImportTables", appending the records to the Master Table.
After importing one table, update the additional column with the table name.

[maggiemago3]

Hi Steve,

Thanks for the direction. Can you walk me through it? I'm new to VBA, and not sure where to start. I've imported multiple tables with multiple worksheets (you helped me on that post). Do I create a code to collect table names after I run my import function? How do I go about the rest of your advice?

[maggiemago3]

Ok, so I've created a query that returns all of the names of the tables in the database, I'm just not sure how to use that to loop through the names and create a master table w/ an additional column that refers to the names

Not hard to do, but it will take VBA to do it.

As you import the Excel files, save the table names to a table (maybe "ImportTables")
Loop through the tables in "ImportTables", appending the records to the Master Table.
After importing one table, update the additional column with the table name.

[maggiemago3]

So I've taken a new direction, instead of importing all files/worksheets as seperate tables, I've imported them all into one master table, however still struggling trying to append the worksheet name into a column.

[ssanfu]

See https://www.accessforums.net/import-...ame-37381.html