ACC: Synch with Replicas Before Changing Design Master Schema

• Microsoft Access versions 7.0, 97

SUMMARY

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

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 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, the potential exists for problems to 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 recreate 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 at 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 at 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 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 turn around and 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.

Example 1 - Table Validation Rules

Suppose you change a table's ValidationRule property for a numeric field in the Design Master database, requiring 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 completes 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

Imagine 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 Customers and Orders. 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 recreate the orders.

NOTE: In Microsoft Access 97, you can remove the relationship from the Design Master, and then synchronize again. This will add the orphaned records to the table in the Design Master, where you will have to resolve the problem of the orphaned Orders records before Microsoft Access will allow you to reapply the enforced relationship between Customers and Orders.

Example 3 - Making a Table Unreplicable, Then Replicable Again

This example illustrates a more serious schema change scenario. Assume 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 recreate the enforced relationship between Customers and Orders. 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 Customers and Orders 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 recreates 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 Customers and Orders is recreated. 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.

REFERENCES

For more information about using replication in Microsoft Access, refer to the replication white papers. You will find download instructions for the white papers in the following articles in the Microsoft Knowledge Base: