Merge two databases

[Brian B]

We have an access 2000 database with with 20 or so tables to keep track of
our inventory. Not long ago I was asked to put a copy on a laptop so the
database could be taken to several remote locations and the information
updated. Ok, so I was in a hurry because it was just before Thanksgiving.
So I simply copied the database to the laptop with the warning, do NOT make
any changes on the server copy. The idea being all I need to do is copy the
backend back to the server when they got back.

Here's the problem. They didn't listen to my warning. Now we have to
copies of the database. Some new records were added in one and some old
records updated in it. Then in the original some records were updated. I'm
pretty sure they did not update the same records in each database.

So, what is the best way to merge the two databases to reflect the updates
and additions?

Thanks guys,
Brian

 

[GVaught]

Personally, I would make them reenter the data they entered to the copy back
into the actual backend. However, having said this, this probably would not
go over well with your co-workers as well as management.

I do not know of any easy way other than a table to table comparison finding
which records exist in the copy that don't exist in the actual backend.
Finding what they updated is even harder as you have no idea which records
were updated and you would have to do a field by field, table by table
comparison.

If this may be an occurrence in the future, I would look into replication.
Something else you could do is create code that copies changes to another
table; listing what the old field data was versus the new data changed or
added, and who changed it (assuming you have security set on the database),
as well as the date it was changed.

 

[Brian B]

I agree totaly. I didn't do the replication thing because it was on one of
our training laptops and was just a temporary thing.

So here's what I ended up doing. Exported the data from the tables to CSV
files from each database. Then ran WinDiff. I then taught the data entry
person how to read the output and had them manually input the data into the
Laptop backend that is now the server backend, as it had the greatest number
of new/changed records.