[Last Call] Learn how to a build a cloud-first strategyRegister Now


Combine multiple databases

Posted on 2006-04-25
Medium Priority
Last Modified: 2012-05-05
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.
Question by:Jsw469
LVL 12

Expert Comment

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

LVL 22

Expert Comment

ID: 16540664
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.


Accepted Solution

jcampanali earned 1000 total points
ID: 16540817
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.
LVL 58

Assisted Solution

harfang earned 1000 total points
ID: 16540875

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!
LVL 12

Expert Comment

by:Bob Lamberson
ID: 16541239
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.


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question