When using database replication, you may occasionally encounter design errors, synchronization conflicts, or synchronization errors. Design errors occur when a design change in the Design Master conflicts with a design change in a replica. The synchronization fails and the content of different replicas starts to diverge. Synchronization conflicts occur when users update the same record at two replicasin the replica set and Microsoft Jet attempts to synchronize the two versions. The synchronization succeeds, but the changes from only one of the replicas are applied to the other. Synchronization errors occur when a change to data in one replica cannot be applied to another replica because it would violate a constraint, such as a referential integrity rule. The synchronization succeeds, but the content of replicas is different.
Synchronization errors and design errors are more significant problems than synchronization conflicts because the replicas no longer share a common design or identical data. This section describes the factors that contribute to conflicts and errors, and suggests ways to prevent or resolve them.
When you make changes to the design of your database, Microsoft Jet records each change in the MSysSchChange system table. When Microsoft Jet applies all the design changes from one replica to another, it applies the changes in the exact order that the changes occurred in the Design Master. This ensures that all replicas become identical to the Design Master.
A design error most often occurs when you attempt to synchronize design changes with a replica that is opened exclusively. A locking error occurs, and the design changes are not transferred to the replica.
A design error can also occur if you set a primary key on a field in your replica and, before you synchronize this change, a user enters a duplicate value in that field in another replica. When you synchronize with the other replica, Microsoft Jet determines that the records do not have unique identifiers and, therefore, the field cannot be used as a primary key.
The MSysSchemaProb table is present only when an error has occurred in updating the design of a replica. The table provides details about the cause of the error, including:
The MSysSchemaProb table is a local table and is not replicated. The records in the MSysSchemaProb table are automatically deleted when the corresponding design change is successfully applied during synchronization.
To correct design errors, use the MSysSchemaProb table to identify the action that failed and then manually correct the corresponding object in the replica. You always fix the problem at the replica even if a design change in the Design Master caused the error.
For example, to correct a locking error that is caused by trying to synchronize with a replica that is open exclusively, close the replica and try the synchronization again.
When you synchronize replicas, conflict between versions is always possible because the same record may be updated at two different locations. If this happens, Microsoft Jet can’t determine which of the two changes should take precedence.
Microsoft Jet accepts the changes from one replica and records the rejected changes in a conflict table in the replica whose changes were not accepted. By default, the record with the most changes since the last synchronization has priority. Microsoft Jet doesn’t look at the content of the data that has changed; instead, it examines the version number of the record. Each time a change is made to the data in a record, the version number increases by one. For example, a record with no changes has a version number of 0. A change to data changes the version number to 1. A second change to the same data, or a change to different data in the record, changes the version number to 2, and so on. The update with the higher version number wins because the Synchronizer assumes that the replica that changed the most frequently is the correct version. When two replicas give an updated record the same version number, Microsoft Jet chooses which update to accept based upon the value of the ReplicaID property. Because you cannot change the algorithm used to decide which changes are accepted and which are rejected, be prepared to manually resolve the errors in any replica.
Conflict tables derive both their names and fields from the underlying tables. Conflict table names are in the form table_conflict, where table is the original table name. For example, if the original table name is Customers, the conflict table name is Customers_conflict. Because conflicts are reported only to the replica that originated the losing update, conflict tables are not replicated.
After synchronizing two replicas, review the database for conflicts and determine whether you need to take any further action. You can determine if a conflict has occurred for a specific table by using the ConflictTable property. This property returns the name of the conflict table that contains the database records that conflicted during synchronization. If there is no conflict table, or if the database is read-only or is a replica, the ConflictTable property returns a zero-length string ("").
You can then examine the conflicts and work through them record by record, fixing whatever is appropriate. For example, you can:
Microsoft Access automatically notifies you of a synchronization conflict and provides the Resolve Conflicts command (Tools menu, Replication submenu) that you can use to view conflict tables and manually resolve each conflict.
You can substitute a custom conflict resolution routine for the Resolve Conflicts command. For example, to find the name of the conflict table associated with a table in your database, examine each record conflict, and take action to resolve each conflict, you can use the following code:
Function ConflictResolver()
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Dim rstConflict As Recordset
Dim rstSource As Recordset
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
' Check to see if the table has a conflict table.
If (tdf.ConflictTable <> "") Then
Set rstConflict = dbs.OpenRecordset(tdf.ConflictTable, dbsOpenTable)
Set rstSource = dbs.OpenRecordset(tdf.Name, dbOpenTable)
rstSource.Index = "s_GUID"
rstConflict.MoveFirst
' Process the conflict table's records.
Do While Not rstConflict.EOF
rstSource.Seek "=", rstConflict![s_GUID]
If Not rstSource.NoMatch Then
' Check to see if the losing record was more recent.
If rstSource.LastUpdated < rstConflict.LastUpdated Then
On Error Resume Next
rstSource.Edit
' Update fields.
For each fld in rstSource.Fields
rstSource(fld.Name) = rstConflict(fld.Name)
Next fld
rstSource.Update
On Error GoTo 0
End If
End If
' Delete conflict record.
rstConflict.Delete
rstConflict.MoveNext
Loop
rstConflict.Close
rstSource.Close
End If
Next tdf
End Function
See Also For more information on custom conflict resolution code, search the Help index for “ReplicationConflictFunction property.”
There are four sources of potential synchronization errors to consider when building your application:
Synchronization errors are recorded in the MSysErrors table and replicated to all replicas in the replica set. This table includes information about the:
Correct errors as soon as possible, because they indicate that the data in different replicas may be diverging. Be especially careful to correct synchronization errors before you move your database, because the error is recorded against the value of the ReplicaID property at the time the error occurred. If the value of the ReplicaID property changes, Microsoft Jet can’t automatically remove the error records during a subsequent synchronization. If you don’t remove the error record, you get an error each time you open the database even if you have corrected the problem.
In many circumstances, errors are self-correcting during the next synchronization. For example, if you attempt to synchronize a record that another user locked, the update fails. Microsoft Jet records an error and attempts to reapply the update at a later time. If the subsequent update succeeds, the error record is removed. As a general rule, always synchronize all replicas in the replica set before manually correcting synchronization errors. Due to the nature of bidirectional synchronizations, it may take more than one synchronization to clear the error record from the MSysErrors table after the error is corrected. However, Microsoft Jet should clear all corrected errors from the MSysErrors table after two bidirectional synchronizations.