Merge Records from Two Identical Databases

November 6, 2000


Several months ago I was asked to create a utility to simplify the process of combining data from identical MS Access databases into one file. Not long after completing that project, someone posted a request to the newsgroup, comp.databases.ms-access, asking for suggestions for doing the same with his data. I replied to his post with an outline of the steps taken to perform my data merge.

Since that time, others too have posted the same request for information on how to merge identical Access databases. While browsing my own site one day, I was reminded of this question and decided that Database Journal readers might be interested in the question as well. Below, I have reproduced the origonal question and reply.

************* Post from Comp.Databases.MS-Access *****************

Subject: Simplest way to merge two database? 
Date:    10/05/2000 

Dear all,
 
I am using Access 2000.
 
I have two identical databases each of them contains the same 
tables like Airline_Booking, Hotel_Booking and Rental_Car_Booking. 
( use auto numbering for primary key ).
 
What is the simplemest way to merge these booking data which 
are contained in two seperate files called Booking_Main_Office.mdb 
and Booking_Branch_Office.mdb? I want to append all records from
Booking_Branch_Office.mdb to Booking_Main_Office.mdb.
 
I have tried the "Get External Data" method but this only copy the 
tables from the branch office database and rename all the tables 
with the same name to diferent names.
 
Anthony

********************** Reply to Post *****************************
Subject: Re: Simplest way to merge two database? 
Date:    10/06/2000 
Author:  Danny J. Lesandrini  

I'll try to keep this simple, but it can get quite complicated when 
you have many data and lookup tables.
 
Let's assume that you have an employee time tracking application.  
It's been deployed at 2 separate sites--  2 separate mdb back end files. 
The program has some tables with unique data, such as tblEmployee and 
tblTimeRecord and some with 'not so unique' data such as tblStatus and 
tblTask.
 
Let's assume that each table has an ID field with an autonumber that 
starts counting from 1.  Both sites will have Employee and Time 
Records from 1 to x.  If you merge Employee records from Site B into 
Site A, you will have to renumber them, starting with the highest 
EmployeeID at Site A + 1.  If you change the EmployeeID for Site B, 
you will have to Cascade those changes to other tables at Site B, such 
as tblTimeRecord. How do you do this?
 
Consider these necessary changes to Site B database ...
 
1) remove the Autonumber from tblEmployee at Site.
2) get the largest EmployeeID from Site A and add 1 to it  (lngAddToID) 
3) make sure that Cascade Updates are in place for every place in the     
   Site B database that has a relationship with EmployeeID
4) run an UPDATE query to add the value, lngAddToID, to EmployeeID    
   for every record at Site B tblEmployee.  The Cascade Updates should   
   propagate those changes throughout your database.
5) link to Site A database and append all the records from tblEmployee     
   in Site B.  Even though Site A still has an Autonumber on EmployeeID,    
   the records will go in without error.
6)  repeat this process for every table with an Autonumber ID
 
That was easy, now wasn't it.  But we haven't gotten to the hard part yet. 
 
The REAL problem comes in when dealing with 'lookup' tables, like the one 
I named tblStatus.  Let's say that each task in tblTask has an associated 
Status.  If each database (Site A and Site B) are installed with a preset 
list of Statuses then there's no problem, but if the user can enter their 
own values, you'll end up with something like this: 
 
Site A tblStatus
1  Open
2  Completed
3  Cancelled
4  Needs Approval
 
Site B tblStatus
1  Completed
2  Open
3  Cancelled
4  Pending
 
In this case you will need to create a mapping table to show how these 2 
status tables relate to each other.  This tblMapStatus is created in the 
Site B database and looks like this.
 
OldID    NewID      Status
---------------------------------
  1        2        Completed
  2        1        Open
  3        3        Cancelled
  4       Null      Pending
 
See the problem here?
 
Site A has a status that we don't see in Site B.  That's not a
problem from one aspect, namely that Site B doesn't use the
status 'Needs Approval' so we don't need to map to it.  But we
do need it's ID number, #4, for our 'Pending' status.
 
So, you need to do as above and go back to tblStatus at Site A
to determine the next available ID, which happens to be 5 and
put that in the tblMapStatus
 
OldID    NewID      Status
---------------------------------
  1        2        Completed
  2        1        Open
  3        3        Cancelled
  4        5        Pending
 
Now, we will have to update our database at Site B by running an Update 
script ...
 
    UPDATE tblStatus INNER JOIN tblMapStatus
    ON tblStatus.ID = tblMapStatus .ID
    SET tblStatus .ID = tblMapStatus .NewID;
 
Well, that's the idea. You simply need to follow the above outline until 
you have taken care of all tables and ID fields.
 
I hope this helps, but I suspect it just makes the task look daunting, 
which it is.  I spent a week creating my Merge Utility and it still has 
bugs, requiring the operator of the utility to intervene from time to time.


See All Articles by Columnist Danny Lesandrini









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers