Merging tables from two Access databases into one new common

I have this assignment that I think someone should be able to help me. I have 5 ACCESS databases wvrapnaoh.accdb, wvrappaul.accdb, ....etc. These databases have about 45 tables each and 15 forms. The good part is the structure, the name and the fields of each table in all the databases are all the same except the data or the records are different. For example I have a stress table in wvrapnoah as well as wvrappaul with the same fields in both tables but different data or records.

So, I need to merge all these five into a new Access database that will have the same structure as the 5 databases but will include the complete data that is all the records from the 5 databases merged into this new database.The same applies 开发者_StackOverflow中文版to even the 15 forms. It does not seem to be having a primary key I guess. I was planning to add a field for each table that would give me the name of the database as well from which it was merged. Example I will add a DBName field in Wvrapnoah in all the tables and add the name Noah in that field for all the records in each table. I basically need to automate this code.

I need a script (VBA or anything) so that the guys creating these databases can just run this script the next time and merge the databases.

Talking about the 'table' part of the problem:

Questions

• Are the databases / table names defined or you don't know them?
• Are you able to use linked tables?

I believe the straightforward way to merge all of them is to link all tables into a single access DB and then run a UNION ALL query. It would be something like this:

SELECT "HANK", *
FROM MyTableHank
UNION ALL
SELECT "JOHN", *
FROM MyTableJohn;

Notice I defined a field to identify the origin of the data being merged ("HANK", "JOHN"), as you suggested above.

About the forms, I believe you'll need to import them and then review the whole code. It basically depends on what the forms are doing. If they're query-based won't be a big deal (importing / fixing the queries, will make the form works). However, if the forms are related to the tables, you'll have more work to do.