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.
Add databasejournal.com to your favorites Add databasejournal.com to your browser search box IE 7 | Firefox 2.0 | Firefox 1.5.xReceive news via our XML/RSS feed
IE 7
Firefox 2.0