Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    16

    Question Unanswered: Merge multiple databases together - best practice

    Hello,
    I'm needing to combine 3 databases into one. Each db has a main table for customers and few connected tables for things like orders, call tracking, etc. Each db has each customer with a unique ID (autonumber). Trying to figure out the best way to combine these 3 dbs and their similarly related tables into one, so one main table for all, one order table for all, etc. and be sure each customer is connected to their respective orders, calls, etc. Thank you for any help.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If each customers table uses an Autonumber as CustomerID, you're in trouble. Simply merging the tables won't work (Access does not have a command such as SET IDENTITY_INSERT <Table> ON found in SQL Server).

    Here's what I would do:

    1. Create a new database

    2. In this database, create a table with exactly the same structure a the table Customers in the 3 existing databases. repeat the process with the other tables.

    Note: You can use the Function GetDDL in the attached file to generate the queries for creating the new tables from the existing ones.

    For clarity purposes, I'll name them:
    - Db (the new database)
    - Customers (table in Db)
    - CustomerID (the Autonumber column in Customers)
    - Orders (table in Db)
    - FKCustomers (the foreing key in Orders pointing to Customers
    - Db1 (the first of the 3 existing databases)
    - Customers1 (the table Customers in Db1)
    - Orders1 (the table Orders in Db1)
    - etc...

    3. Make a backup of the 3 existing databases and copy them in a safe place.

    4. Import the tables Customer1, Orders1, etc. in Db.

    5. use a procedure such as:
    Code:
    Dim rst As DAO.RecordSet
    Dim strSQL as string
    Dim OldID As Long
    Dim NewID As Long
    
    
    Do
        strSQL = "INSERT INTO Customers (<List of Fields in Customers without the Autonumber>) SELECT TOP 1 <List of Fields in Customers1 without the Autonumber> From Customers1 ;"
        CurrentDb.Execute strSQL, dbFailOnError
        OldID = DMin("CustomerID", "Customers1")
        NewID = DMax("CustomerID", "Customers")
        ' Update the table Orders1.
        STRSQL = "UPDATE Orders1 SET Orders1.FKCustomers = " & NewID 
        Currentdb .Execute strSQL, dbFailOnError
        ' Repeat the process with the other tables having a Foreing key to Customers1.
        strSQL = "DELETE FROM Customers1 WHERE Customers1.CustomerID = " & OldID & ";"
        Currentdb .Execute strSQL, dbFailOnError
    Loop While DCount("*", "Customers1") > 0
    6. Use queries to insert the rows from Orders1, etc (NOT Customers1!) into Orders, etc.:
    Code:
    INSERT INTO Orders (<List of Fields in Orders>) SELECT <List of Fields in Orders1> FROM Orders1;
    Repeat for each table imported from Db1 (NOT Customers1!)....

    7. Delete the tables imported from Db1.

    8. Repeat the process whith Db2 and Db3.

    9. The only thing you have to do now is to identify customers that were imported more than once because the same customer was in several of the databases and use a procedure to correct the problem. If there are few duplicates, you can do it manually.

    The process will be slow but since you only need to perform it once, it does not really matter.
    Attached Files Attached Files
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •