Sync non-replica database copies (1 Viewer)

[seamusnboo]

Hi,

I wonder if somebody can help me with this. At work, we have created a database to store water quality of various swimming pools throughout the day. We started entering some past data to the database and at some point somebody had made a copy of the database. They believed that they were using a replica, but in fact it was just a copy. So now we have two versions of the database, one with some data the other one doesn't have and vice-versa. We're unable to synchronize the databases because they're not replicas. Does anybody have any idea how we can synchronize the data other than making it manually?

Thanks

 

[The_Doc_Man]

First, find the user that made the copy. Escort him/her behind the building. Have Cousin Guido persuade them to not do it again. The uglier Cousin Guido is, the more likely the lesson will stick. Particularly if Guido carries an old Louisville Slugger with a few chips in it. OK, just kidding...

Second, open the improper copy. Rename the tables from which you want to recover data. You are doing this to avoid table-name conflicts and you are planning to round-file this DB when you are done, so it DOES NOT MATTER if the rename happens to break links. If you have to cancel all relationships in this case, it is OK. Close the copy.

Third, open the "real" copy. Create (linked) tables from the improper copy.

I'm going to presume that you can identify the records that are common to both, perhaps by some key OTHER than an autonumber. Write an append query from the linked table to the real table, selecting only those records that you have identified as NOT being in common to both tables. Do this for each table. You might have to do this append operation for parent tables first, because otherwise you might have issues in relational integrity. You will also have to assure that child tables can translate their foreign keys, which might require a separate concordance table.

When done, delete the links. Delete the invalid copy. If the offending user is not yet repentant, delete the user.