Combine multiple databases

I have multiple database that I need to combine into 1.  They all have the same password to open and all have the same structure.  I need to combine all the data in all the tables.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LambersonSoftware EngineerCommented:
How many databases and tables in each, are there?
Will you append all the records or will there be some duplicates that need attention?

You can choose one of your databases and then link the tables from the others to it. Just right-click in a blank area of your tables and select link tables.

I think there are way more issues than what is presented here.  When you attempt to combine data in multiple DBs into one DB into the same tables, you run into lots of issues.  For example, let's say you have tblCustomers and tblOrders (classic book example).  Then let's say your Primary Key in tblCustomers is CustomerID and you defined that field as an AutoNumber data type.  Ok, now, your soundly designed database has CustomerID in tblOrders as a foreign key. So what's the problem.  Well, all your databases will have used the same CustomerIDs.

Easy fix if only a few tables affected.  More complex if many tables.  I look at the max CustomerID in DB #1.  Say it is 3792.  Well I add 3792 to all CustomerIDs in DB2 in tblCustomers and tblOrders using Update Query.  Then you can append (with linked tables) into DB#1 tables.  Continue this process for all DBs.  i.e. For DB#3, look at new max in DB#1 and add to DB#3 tables and so on.

I'll elaborate if I'm headed in the right direction for you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Although the theory is complex (meaning there is no good general solution for database merging), in most cases simple solutions can be found. The tricky problems are:

1) identifying identical or similar records in the various databases
2) managing conflicting keys of different records.

We has this topic last year, and after much discussions, a very simple solution could be used. Take a look at {http:/Q_21594247.html} and my accepted suggestion.

Tell me if this could fit your scenario.
Good luck!
Bob LambersonSoftware EngineerCommented:
Another question is if this is a one time situation and after you combine all, will you have to repeatedly combine them, or will you be able to work with the combined set in the future. This can be done fairly easily for a one time, few databases/tables scenerio, but can get complicated if there are very many.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.