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.