DB synchronization after corruption

[Spark_GGSE]

Hi all - please forgive any newb questions I may ask, as I'm a network/server guy by trade. My Access knowledge is limited to what I've needed to learn to keep an app of ours up and running. And I've run into an issue that I can't get straight. So here's the situation, I'll keep it as brief as possible:



We have an Access DB (2003 format using 2007), that has a front end, user facing component, and of course, the actual data holding back end. It is set up to be replicable between machines in the office, as two of the people in the office do field work. When they come back from their field work, they're supposed to sync with the main desktop PC. As we all know, users don't always do what they're supposed to. So what's happened - the master DB had a memo field in a record that became corrupted at least a month ago. This went unnoticed until last week, when one of the field agents attempted to sync. Upon doing so, he received the error "Record is deleted". So I made copies of both his backend DB, and the one on the desktop. I hunted down the corrupted record, which wouldn't allow me to delete it. I did a little research and self-education, and figured out how to recreate the table (and all it's relationships) which had the corrupted record in it, and then run an append query to repopulate it without the corrupted record. The issue now is that I still can't get the two back ends to sync - I get a "The search key was not found in any record" error. With the regenerated table, I can create a new replica, and that synchronizes fine.

Any assistance would be much appreciated. Since the corruption went unnoticed for so long, a lot of data has been entered into both replicas, so having to choose one over the other isn't really a viable solution. One thought that crossed my mind is to see if I can extract all the data out of the field agent's machine that had been added over the past 30 days and then somehow append it to the repaired DB, but I don't see where the person who developed this system included any sort of date record for me to specify that...

[rpeare]

A tough situation. What are the primary keys of the tables involved, are they autonumbers or do they have a generated PK? I've got to think it's a generated PK otherwise you wouldn't be able to synchronize the databases and maintain the relationships correctly. If that's the case how is the PK generated? Maybe there's a way to dissect the PK to get an idea of which are the new records. or you can flat out append all records from the 'corrupt' version (excluding the corrupted record itself) that do not exist in the regenerated data. The second method would exclude an updates that were performed to 'old' data which you'd likely have to update in a separate query. I don't know the nature of your data etc so it's hard to suggest one over the other but I would be inclined to look at data that exists in your pre-restoration data that do not exist in your after restoration data and use that as your means to append new data.