Handling Conflicts and Errors

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.

Design Errors

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.

Correcting Design Errors

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.

Synchronization Conflicts

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.

Resolving Synchronization Conflicts

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.”

Synchronization Errors

There are four sources of potential synchronization errors to consider when building your application:

Correcting Synchronization Errors

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.