Replication mess

[pshaw]

I have a client that I set up years ago with replicated databases, Access 2003. Somehow the design master no longer sees itself as either a replica or design master. I need to synchronize it with the replica which they decided to use as the master for at least the last 2 months. I tried to make a replica of the jacked up database but it errors out with a missing systable. So then I created a new database and imported in the tables and created the replica thinking I could then synchronize with the replica that is good....wrong, says cannot because they are from 2 different replicas. Does anyone know how I can get these 2 databases synchronized? Thanks.

[ItsMe]

I would not expect the sys tables to be much help. Perhaps you can use queries to look for fields that need to be updated. Then use queries for appending records.

[CJ_London]

A replica can only sync with the master from which it came - and masters can only sync with its own replicas. Once you lose the relationship you can no longer sync.

If they are only using the replica, then you can just convert this to a master and generate new replicas, deleting all the old ones.

However if they have been using the master as well, and it is only a matter of a few records then the easiest thing to do is to manually compare. Otherwise you potentially have a major task

I seem to recall replication uses three fields in addition to the primary key - a replication field and two timestamp fields, one for date/time of last time the record changed on a sync and one for the last time the record was changed by the user or other process. There may be third one for date the record was created, I can't remember

It has been a few years since I have had to do this so I may have missed something but in principle you need to compare the two tables one at a time- I recommend you work from a new db and broadly follow this process a table at a time. Basically create a new master db from which you can generate new replicas. It is not syncing as such (where both tables get updated) just blending the data to create the final view.

1. Copy the db you believe to be most up to date into a new db so the original remains if you have to start again
2. a table at a time, link to the equivalent table in the other db - it will automatically add 1 as a suffix to the table name
3. you then need to compare each table both ways linking on the replicationID - to determine what is in both tables - uses an inner join
-- if a match then compare the timestamp fields
---if both the same then the records are the same so save
----if timestamps are different then save the one with the latest timestamp - but this is where I'm a bit hazy. One of the timestamps is when it was last updated on a sync, the other is when it was last updated by the user - so if you match on the sync but have two later user update dates then you have to decide which is actually the latest version
4. then using a left join (find unmatched) find records in one table and not in the other.
-again you will need to look at the timestamp fields - it may be a record has not been found because it is 'new' in one table and not been added to the other, or it may be it originally existed in both tables but has been deleted from the second one - in which case it needs to deleted from the first one - you'll need to decide.
5. reverse 4 to find missing records 'the other way'

I would go through the tables in logical 'relationship' order - for example 'blend' customers before invoice headers and products and do those before invoice lines - so if necessary you can lookup the id to a parent which might change as part of getting everything up to date

Finally while you are doing this, users must not add or change any records

Sorry I can't provide you with a full solution but hopefully this will get you going.

You might want to consider updating the backend to SQL server (master) and SQL Server Express(for replicas) which is much more robust, but obviously has a cost. Depending on why you are using replicas, it may be you can look at moving it to Azure or using something like citrix or terminal server. Replicas these days are really only relevant if users do not have a decent network connection or are out in the field with no access at all.

[ItsMe]

Sorry I can't provide you with a full solution but hopefully this will get you going.

I would say that you offered a significant amount of detail.