ACC: How to Use the ReplicationConflictFunction Property

This article was previously published under Q158930
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article show you how to use the ReplicationConflictFunction propertyin a database replica set so that you can create a custom procedure toresolve synchronization conflicts.
When you use replicated databases in Microsoft Access, you may encountersynchronization errors from time to time. Microsoft Access includes aConflict Resolver wizard to help you resolve those conflicts when theyoccur.

However, you may want to create your own procedure to help users resolvesynchronization conflicts. Also, the Microsoft Access Developer's Toolkitlicense agreement does not permit you to distribute wizards with yourrun-time applications; therefore, you must create your own conflictresolution procedure if your run-time application includes replicateddatabases.

The ReplicationConflictFunction property enables you to use a customprocedure to resolve conflicts instead of using the Conflict Resolverwizard. The procedure that you create to resolve conflicts must be in areplicable module in the Design Master.

Creating a Custom Conflict Resolver

Your custom procedure must be able to process three types of errors:
  • Synchronization conflicts
  • Synchronization errors
  • Design errors

Synchronization Conflicts

Synchronization conflicts occur when one record in a table is updated attwo or more different replicas. Even if the changes are made to differentfields in the record, the Microsoft Jet database engine treats it as aconflict. The Jet database engine does not try to resolve the conflict.Instead, it uses an algorithm to select one version of the record as theofficial change, and it stores the other version in a conflict table.

Conflict tables are named <tablename>_Conflict, where <tablename> is the table in which the conflict occurred. You can detect conflicts using Visual Basic code by looking for the ConflictTable property of a table. The following portion of a procedure checks for the existence of conflict tables in a replica database:
   Sub ViewSyncConflict()      Dim Db As DATABASE      Dim Td As TableDef      Dim i as Integer      Set Db = CurrentDb      ' Step backward through the TableDefs collection so you      ' do not miss any tables when you delete conflict tables.      For i = Db.TableDefs.Count - 1 to 0 Step -1         Set Td = Db.Tabledefs(i)         If (Td.ConflictTable <> "") Then            ' Open a recordset based on the conflict table.            ' Insert code to do conflict resolution.            ' Delete the conflicting record when you are done.            ' Delete the conflict table when all its records are deleted.            ' Set the ConflictTable property to "".         End If      Next i   End Sub

Synchronization Errors

  • A table-level validation rule is applied and other replicas have data that does not satisfy the rule.
  • A duplicate key error occurs because two different replicas entered the same primary key into a table.
  • A referential integrity error occurs because a primary table record is deleted in one replica, and a record is added to a related table in another replica.
  • A record is locked when synchronization occurs.
Synchronization errors are recorded in the MSysErrors table and arereplicated to all members of the replica set. Once you have corrected theerrors, the Jet database engine automatically removes the error recordsduring a subsequent synchronization.

Open the MSysErrors table to see what errors occurred. The followingportion of a procedure displays the synchronization errors in theMSysErrors table:
  Sub ViewSyncError()      Dim Db As DATABASE      Dim Rs As Recordset      Dim MsgString As String      On Error GoTo ErrorHandler      Set Db = CurrentDb      Set Rs = Db.OpenRecordset("MSysErrors", dbOpenSnapshot)      Rs.MoveLast      If Rs.RecordCount > 0 Then         Rs.MoveFirst         Do Until Rs.EOF            ' Build the error message string.            MsgString = "Table ID: " & Rs!TableGUID & vbCr            MsgString = MsgString & "Record ID: " & Rs!RowGUID & vbCr            MsgString = MsgString & "Operation: " & Rs!Operation & vbCr            MsgString = MsgString & "Failed Because: " & Rs!ReasonText            MsgBox MsgString            Rs.MoveNext         Loop      End If   ExitProc:      Exit Sub   ErrorHandler:      ' If the MSysErrors table is empty...      If Err.Number = 3021 Then         Resume ExitProc      ' display any other error that occurs.      Else         MsgBox Err.Description         Resume ExitProc      End If   End Sub				

Design Errors

A design error occurs when a local object exists with the same name as areplicable object in the Design Master. For example, if a user at a replicacreates a local form called Form1, and you create a replicable form in theDesign Master called Form1, synchronization fails. The design error isrecorded in a system table called MSysSchemaProb. The records inMSysSchemaProb are automatically deleted when the conflict is resolved andthe design change is successfully synchronized.

The following sample procedure checks for the existence of theMSysSchemaProb table, and displays the design error:
  Sub ViewDesignError()      Dim Db As DATABASE      Dim Rs As Recordset      Dim MsgString As String      On Error GoTo ErrorHandler      Set Db = CurrentDb      Set Rs = Db.OpenRecordset("MSysSchemaProb", dbOpenSnapshot)      Rs.MoveFirst      Do Until Rs.EOF         ' Build the error message string.         MsgString = "Operation: " & Rs!Command & vbCr         MsgString = MsgString & "Failed Because: " & Rs!ErrorText         MsgBox MsgString         Rs.MoveNext      Loop   ExitProc:      Exit Sub   ErrorHandler:         ' If the MSysSchemaProb table does not exist.         If Err.Number = 3078 Then            Resume ExitProc         ' If the MSysSchemaProb table is empty.         ElseIf Err.Number = 3021 Then            Resume ExitProc         ' Display any other error that occurs.         Else            MsgBox Err.Description            Resume ExitProc         End If   End Sub				

Putting It All Together

After you have decided how you want to handle each type of synchronizationerror, create the custom function to use when conflicts occur. Thefollowing example combines the sample procedures in each of the earliersections into a single function:
   Function MyCustomFunction()      ViewSyncConflict      ViewSyncError      ViewDesignError   End Function				

Setting the ReplicationConflictFunction Property

You can set the ReplicationConflictFunction property programmatically orthrough the user interface. When you set the property programmatically, youmay have to add ReplicationConflictFunction to the Properties collection ofthe database first, and then set its value.

Setting the ReplicationConflictFunction Programmatically

  1. Open the Design Master database for the replica set.
  2. Create a module and type the following procedure:
          Sub SetCustomFunction(FunctionName As String)         Dim Db As DATABASE, Ctr As Container, Doc As Document         Dim Prp As Property         On Error GoTo ErrorHandler         Set Db = CurrentDb         Set Ctr = Db.Containers!Databases         ' Set Document variable pointing to user defined document.         Set Doc = Ctr.Documents!UserDefined         ' Set the ReplicationConflictFunction property if it exists.         Doc.Properties!ReplicationConflictFunction = FunctionName      Exit Sub      ErrorHandler:         ' If the property does not exist...         If Err.Number = 3270 Then            ' create ReplicationConflictFunction property and set its            ' value.            Set Prp = Doc.CreateProperty("ReplicationConflictFunction", _                dbText, FunctionName)            ' Append the new property to the collection.            Doc.Properties.Append Prp            ' Resume the main procedure.            Resume Next         Else            ' Display any other error that occurs.            MsgBox Err.Number & ": " & Err.Description, vbCritical         End If      End Sub					
  3. To test this function, type the following line in the Debug window,and then press ENTER:

Setting the ReplicationConflictFunction Through the User Interface

  1. Open the Design Master database for the replica set.
  2. On the File menu, click Database Properties.
  3. In the <DatabaseName> Properties dialog box, click the Custom tab.
  4. Set the following properties:
    Name: ReplicationConflictFunction
    Type: Text
    Value: MyCustomFunction()
  5. Click OK to close the Properties dialog box.
For more information about using replication in your database, pleasesee the following articles in the Microsoft Knowledge Base forinstructions on obtaining the Microsoft Jet Database Replication whitepaper:
138828ACC95: Microsoft Jet Replication White Paper Available in Download Center

164553 ACC97: Jet 3.5 Replication White Paper Available in Download Center

Article ID: 158930 - Last Review: 12/04/2015 15:45:01 - Revision: 3.2

Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition, Microsoft Access Developer's Toolkit 1.1, Microsoft Office 97 Developer Edition

  • kbnosurvey kbarchive kbhowto kbprogramming KB158930