Merging Data From Duplicate Database Files

[ComputerPower]

Hello,



We have a client who has managed to muck up their Access database by means of having two users on two computers attempting to use and modify it at the same time, and the primary (actually, the only) location for the database is on Dropbox.

What seems to have happened is that Dropbox detected a conflict, with two different versions of the same file, and resolved this by creating two separate files. How long this went on before the client noticed that they were entering data into two separate files, I can't say.

Their client list is 28,000 someodd people long. What I need to know is if there's a process for merging the two files together, without creating 28,000 duplicate entries.

This is being run on Access 2002.

[Rawb]

That... is potentially a very nasty problem.

If you don't use Autonumber Columns anywhere, you should be fine to just copy over any unique Records.

If you do use any Autonumber Columns, the solution is going to be a little more complex:

1. First off, label one of the databases as the "main" db and the other one the "secondary" db (you can decide which is which at random if you want).
2. Then, in the secondary db, do the following:
   
   1. Break any Relationships involving an Autonumber Column.
   2. Change all the Autonumber Columns to "Long Integer" Numbers.
   3. Restore the broken Relationships to the no-longer-Autonumber Columns.
   4. Remove all non-unique Records from the secondary db so only the Records you will be merging into the main db remain.
   5. Find the next available Autonumber value (from the main db) for each no-longer-Autonumber Column in the secondary db.
   6. Update the no-longer-Autonumber values so that they start at that next available number (from the main db).
   7. Verify that the Relationships are intact and the child Records updated to the new no-longer-Autonumber values correctly.
   8. Append the data from your secondary db into your main db.
   
   

Obviously, you wil want to make sure you have valid backups of both dbs before starting. You will also need to be able to perform the entire merge during a time when neither database is being used.

[orange]

Bob Larson has a merge analysis tool on his site that may be applicable.
I don't think anyone uses a database on dropbox for multiuser activity????