Microsoft KB Archive/207787

= ACC2000: Synchronize with Replicas Before You Change Design Master Schema =

Article ID: 207787

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207787



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
This article explains the importance of synchronizing all the databases in a replica set before you make schema changes in the Design Master database.



MORE INFORMATION
The schema changes that this article discusses are limited to any changes that you make to the design of your tables or their relationships. When you make schema changes in the Design Master database of a replica set, carefully consider the impact that those changes may have on other replicas in the set. For example, if you create a one-to-many relationship that enforces referential integrity between two tables, one of the replica databases may contain orphan records in the child table that do not have a corresponding parent record. Or if you change a table validation rule, there may be records in a replica table that do not comply with the new validation rules.

Whenever you change the schema at the Design Master, problems may occur when you synchronize with other replicas. Those problems may range from simple synchronization errors to more complex design errors. It is possible to create a situation where you can no longer synchronize with another replica, and you must delete and re-create it.

For these reasons, it is strongly recommended that you synchronize with all members of the replica set before changing the database schema in the Design Master, and then suspend data entry in each of the replicas until those schema changes have been synchronized throughout the replica set.

To understand the impact your changes can have on the replica set, it helps to know how Microsoft Access synchronizes data and design changes between replicas:


 * When you make design changes to the Design Master database, each change is stored as a separate record in a Microsoft Access system table named MSysSchChange.
 * When you synchronize with a replica, all changes are applied to the replica in the same order that you made them in the Design Master. Microsoft Access does not examine all records in the MSysSchChange table to see if a design change that you made was undone or modified at a later time. For example, if you create a new form in the Design Master and make it replicable, and then you delete the form, when you synchronize with a replica, the form is first created, and then deleted there, as well.
 * When you synchronize with a replica, all design changes are applied first before any data is exchanged. If a design change cannot be applied, an error occurs and synchronization stops until the conflict can be resolved.

This method of synchronizing ensures that all replicas become identical to the Design Master before any data is exchanged. It also can be the source of problems or frustration if you do not plan your changes carefully. Each of the sample scenarios that follow help to illustrate the types of situations you may encounter if you do not synchronize all replicas before you implement schema design changes. For simplicity, each example assumes that your replica set consists of a Design Master and one replica database.

Example 1 - Table Validation Rules
Suppose that you change the ValidationRule property of a table for a Number field in the Design Master database to require that the field value must be greater than 50. When you synchronize with a replica that contains a record with the number 48 in that field, synchronization is completed successfully, but a data error occurs. This is because the table in the Design Master could not be updated with the data from the replica table that does not fit the new validation rule. To resolve this error, you must change the data in the replica so that it complies with the validation rule, and then synchronize again.

Example 2 - Creating a Relationship
Suppose you have a Customers and an Orders table that have no enforced relationship. In a replica database, someone adds some records to the Orders table for which there are no records in the Customers table. Meanwhile, in the Design Master database, you create an enforced relationship between the Customers and Orders tables. When you synchronize, you receive an error message that synchronization failed because a design change could not be applied. This is because there are orphan child records in the replica database's Orders table that have no matching customer in the Customers table, which means that the relationship between Customers and Orders cannot be applied. You must either create records in the replica's Customers table that correspond with each orphaned record in the Orders table, or you must delete the orphaned Orders records until after you synchronize with the Design Master, and then re-create the orders.

Example 3 - Making a Table Unreplicable, Then Replicable Again
This example illustrates a more serious schema change scenario. Suppose that you have two tables called Customers and Orders with a one-to-many relationship that enforces referential integrity. In the Design Master database, you remove the relationship between the two tables, and you make the Customers table unreplicable. Then you change your mind, and you make it replicable again, and re-create the enforced relationship between the Customers and Orders tables. When you synchronize with a replica, the synchronization will fail because the design changes cannot be applied. Here is what happens in the replica database:
 * The relationship between the Customers and Orders tables is deleted.
 * Because the Customers table was made unreplicable in the Design Master, it is deleted from the replica database.
 * Because the Customers table was made replicable again, Microsoft Access re-creates the structure of the table in the replica, but does not add any data because data exchange always happens after design changes have been applied.
 * The relationships between the Customers and Orders tables is re-created. This is when synchronization fails because there is no data in the Customers table yet, and Microsoft Access cannot create an enforced relationship with the Orders table that contains orphaned child records.

In this scenario, you must re-create the replica from the Design Master. No amount of data manipulation will allow you to synchronize successfully. In this case, the problem could be prevented by synchronizing the Design Master with the replica after you make the Customers table replicable again, but before you reapply the relationship. Then, re-create the relationship in the Design Master, and synchronize again.

