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, 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 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.
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 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.
In this scenario, you must recreate 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 re-apply the relationship. Then, recreate the
relationship in the Design Master and synchronize again.
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:
138828 ACC95: Microsoft Jet Replication White Paper Available in Download Center
164553 ACC97: Jet 3.5 Replication White Paper Available in Download Center