Resolving Conflicts by Using DAO

After synchronizing two replicas, you should review each conflict to determine whether the correct information was applied to the database. 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. For example, to find the name of the conflict table and examine and resolve each record conflict, you can use the following code. This is a generic function into which you can plug your own custom conflict-resolution code:

Sub ResolveConflicts(strDbPath As String)
	Dim dbs As Database
	Dim tdf As TableDef, rst As Recordset
	
	Set dbs = OpenDatabase(strDbPath)
	For Each tdf In dbs.TableDefs
		If (Len(tdf.ConflictTable) > 0) Then
			Set rst = dbs.OpenRecordset(tdf.ConflictTable)
			' Process each record.
			With rst
				.MoveFirst
				Do Until .EOF
					' Perform conflict resolution.
					' Remove conflicting record when finished.
					.Delete
					.MoveNext
				Loop
				.Close
			End With
		End If
	Next tdf
End Sub

If there is no conflict table, or if the database is nonreplicable, the ConflictTable property returns a zero-length string.

As you review each conflict in the conflict table, you should take the appropriate action. If the record version selected by Microsoft Jet was the correct version and no further action is necessary, you can delete the record from the conflict table. If the record version selected by Microsoft Jet was not the correct version, you might want to:

Manually enter the data from the conflict table in the database.
Develop a custom conflict-resolution routine that always assigns a higher priority to changes in one specific replica over another replica.
Consider whether procedural changes regarding how data is entered or changed in records are necessary.

The following code is an example of a custom conflict-resolution procedure. This function compares the conflicting records and determines which one was entered most recently. The most recently entered record wins the conflict. For this procedure to work, each table must have a MostRecentUpdate field that is updated to the current time each time the record is updated.

You would run this procedure after a replica has been synchronized with another replica in the replica set. At this point Microsoft Jet will have performed its conflict resolution, so that the record that was changed the most times or that has the lowest ReplicaID property value “wins” the conflict and is written into the source table in both replicas. The losing record is written into the conflict table in the replica in which the record was originally entered.

This function iterates through each TableDef object in the database, looking to see if it has a conflict table by checking the TableDef object’s ConflictTable property. When a conflict table is found, it opens up recordsets on both the source table and the conflict table. The procedure then moves through each record in the conflict table, and uses the Seek method on the s_GUID field, which exists as a unique index in both tables, to compare the source table record and the conflicting record. It checks the MostRecentUpdate field in both tables, and if the source table (which contains the winning record) was not updated last, this record is replaced by the (former) losing record in the conflict table. Regardless of whether it wins or loses, the conflict record is then deleted.

Note that in order to use this function to resolve conflicts on a table in your replicable database, you must add a Date/Time field named MostRecentUpdate to the table. You must also ensure that every time a user updates a record, the time at which the update occurred is stored in the MostRecentUpdate field.

Function ConflictResolver()
	Dim dbs As Database
	Dim tdf As TableDef
	Dim fld As Field
	Dim rstConflict As Recordset
	Dim rstSource As Recordset

	Set dbs = DBEngine(0)(0)

	For Each tdf In dbs.TableDefs
		' Check to see if table has a conflict table.
		If (tdf.ConflictTable <> "") Then
			Set rstConflict = dbs.OpenRecordset(tdf.ConflictTable, dbOpenTable)
			Set rstSource = dbs.OpenRecordset(tdf.Name, dbOpenTable)
			rstSource.Index = "s_GUID"
			rstConflict.MoveFirst
			' Scroll through conflict table's records.
			Do While Not rstConflict.EOF
				rstSource.Seek "=", rstConflict![s_GUID]
				' Make sure match was retrieved in source.
				If Not rstSource.NoMatch Then
					' Check to see if loser was more recent.
					If rstSource!MostRecentUpdate < rstConflict!MostRecentUpdate Then
						On Error Resume Next
						rstSource.Edit
						' Scroll through fields to update.
						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

When this function copies the conflict table data over the source table data, it disables error handling and uses a For Each...Next loop to iterate through all the fields. Because all the replication system fields are read-only, the function can’t update them and would return an error if error handling were enabled.

There are two problems with resolving conflicts so that the most recently updated record is the winner. The first problem is that computer clocks are not synchronized on a network, so you can’t perform a completely accurate comparison to determine which record was updated last. The second problem occurs when your users have replicas in different time zones. If this scheme doesn’t work for your application, there are a variety of different conflict-resolution schemes that you can implement. You can use the function above as a starting point, and develop your own conflict-resolution paradigms, depending on your needs.