Combine multiple databases

Posted on 2006-04-25
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

    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

    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.

    LVL 4

    Accepted Solution

    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


    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

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    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…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now