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.