In Access 95 and 97, there were synchronization conflicts and synchronization errors. Synchronization conflicts occurred when two users updated the same record in two different databases in a replica set. Synchronizing the two databases would succeed, but only one of the two sets of changes would be applied to both databases. Synchronization errors occurred when a change to data in one database in a replica set could not be applied to another database in the replica set because it would violate referential integrity or some other constraint.
In Access 2000 and later versions, the events that cause synchronization conflicts and errors are both viewed simply as synchronization conflicts. A single mechanism is used to record and resolve the conflicts, making the process much easier. Whenever a conflict occurs, a winning change is selected and applied in all replicas, and the losing change is recorded as a conflict at all replicas. The Conflict Viewer, the default tool in Access, is used to reconcile and resolve synchronization conflicts.
A comparison of row and column level conflict resolution between different versions of Access
In Microsoft Access 95 and 97, conflicts are determined at the row-level. Conflicts will occur whenever the same record is changed in two separate replicas, even if different fields are changed at each replica. For example, changing a customer's phone number in one replica and that same customer's zip code in another replica will cause a conflict.
In Access 2000 and later versions, conflicts are determined at the column-level. Changes to the same record in two different replicas cause a synchronization conflict only if the same column, or field, is changed. So in the previous example, there would no longer be a synchronization conflict since the two users changed different fields.
Column-level conflict resolution is the default when a database is made replicable. To specify row-level conflict resolution for a table, you must set it prior to making the table replicable.
You can set the conflict tracking on a database or an individual table prior to making it replicable. Once you create a replica, you can’t change the setting. However, you can change the value of individual tables to row-level tracking.
Note By using Jet and Replication Objects (JRO) you can only specify the default tracking for the database, not for individual tables. When making a database replicable, you can set ColumnLevelTracking to either True or False by using the JRO MakeReplicable method.
Types of synchronization conflicts
Simultaneous update conflict
A simultaneous update conflict occurs when two replicas both update the same record. The losing record is logged in the conflict table.
Unique key conflict
A unique key conflict can occur in one of the following ways:
- Two replicas both enter a new record with the same value in a field that is a primary key or has an unique index.
- The Design Master creates a unique index, and a replica simultaneously adds two or more records with the same key value. When the schema change reaches the first replica with duplicate key records, then the first duplicate key record remains in the base table. Subsequent records are marked for deletion and are written to the conflict table.
Table-level validation conflict
A table-level validation conflict occurs when data is entered that breaks a table-level validation rule. A validation rule restricts the values or types of data that can be entered into a table. If you add a table-level validation rule to the Design Master without determining if any existing data violates the rule, you may encounter a conflict when you synchronize the design changes to the rest of the replica set. In this case, the records are deleted and logged in the conflict table.
Update referential integrity conflict
An update referential integrity conflict occurs when the primary key is updated at one replica and new child records that reference the original primary key value are added at a different replica. During synchronization, the new child records are deleted and logged in the conflict table. To reduce the number of these types of errors during synchronization, you may want to use the s_GUID feld as the primary key in some or all of your replicated tables.
Delete referential integrity conflict
A delete referential integrity conflict occurs when a primary key record is deleted in one replica while new child records that reference the deleted primary key are added at a second replica. When the two replicas are synchronized, the new child records are marked for deletion and added to the conflict table.
Locking conflict
A locking conflict occurs when the record cannot be applied during synchronization because another user locked the table. Microsoft Access will try to update the record several times, but if unsuccessful, the synchronization will stop, and the entire transaction will return to its original status. An error is returned but no conflict is logged.
Foreign key violation conflict
A foreign key violation conflict occurs when there is an invalid primary key record. This could be caused by any of the other conflict types.
Case-sensitivity and sort order conflicts
The number of conflicts can increase in replica sets that reside across multiple database types
Conflicts in partial replicas
A partial replica receives conflicts associated with all of the rows in the partial replica, even for those rows that are added to the partial replica during synchronization.
If the same field in a replicated database is changed in one or more replicas, conflicts result when you synchronize a replica with the replica set. To resolve these conflicts, open the Conflict Viewer. The wizard presents each conflict, and you must manually determine which changed record contains the correct information.