Recipe 10.2 Maintain Multiple Synchronized Copies of the Same Database
You have a database that
you'd like to distribute to mobile salespeople.
Multiple users update the central copy of the database on a daily
basis, and the salespeople also need to make updates to their own
copies of the database. Is there any way to let everyone make updates
and synchronize these copies when a salesperson returns to the office
and plugs into the network?
95 introduced a powerful feature called
replication, which allows you to keep multiple
copies of the same database synchronized. Subsequent versions of
Access have continued to improve on replication. In this solution, we
discuss how to set up a database for replication, how to synchronize
the replicas, and how to deal with synchronization conflicts.
Although it's easy to implement,
it's difficult to undo the effects of replication.
We recommend that you create a copy of your database and work with
that copy while learning about replication. Do not experiment with a
production database until you are ready to handle any problems that
10.2.2.1 Replicating a database
The steps for replicating a database using the Access menus are as
Back up the database and safely
store the backup.
Select Tools Replication...
Create Replica. A dialog will appear informing you that
the database must be closed before you can create a replica and that
the database will increase in size. Choose Yes to proceed. A second
dialog will ask you if you want to make a backup of the database
before replicating it. Choose Yes if you didn't make
a backup in Step 1, or No if you did. If you choose Yes, a backup of
your database will be made with the .BAK
extension. For example, the sample database
10-02.MDB will be backed up to
will then be prompted for the location of a replica. Access will
create a design master replica, which takes the
name of your original database, and a second replica of the design
master, the name and location of which this dialog prompts for. You
will end up with two identical databases. The dialog shown in Figure 10-10 is displayed on completion of the creation of
the replicas, to inform you of the name and location of both the
design master and the replica.
Figure 10-10. The create replica progress dialog
Once you click OK, the replication process is complete, and you will
see the database container of the design master replica of the
original database, as shown in Figure 10-11.
Figure 10-11. The database container of the replicated 10-02 database
You can create additional replicas by opening an existing replica and
selecting Tools Replication... Create Replica.
Access allows you to create additional replicas from any member of
the replica set. However, you can make design changes only in the
design master replica.
Distribute the replicas to
the salespeople's laptops. Do
not copy replicas to multiple machines using DOS
or the Windows Explorer. You must create an additional unique replica
for each user who will be using the replicated database by choosing
Tools Replication... Create Replica from the
menu and specifying each laptop as the destination.
10.2.2.2 Synchronizing replicas
Replicas in a replica set remain independent of each other until you
choose to synchronize them. You can synchronize only replicas that
are members of the same replica set; that is, only copies derived
from the same design master. You synchronize replicas a pair at a
time. When you are ready to synchronize a pair of replicas—for
example, when a salesperson returns to the office and plugs his
laptop into the office network—follow these steps:
Start Access and open any of the replicas in the replica set.
Select Tools Replication...
Using the drop-down box, select the database with which you wish to
synchronize (see Figure 10-12). If you
don't see the replica you want to synchronize with,
someone has probably moved it, so you'll need to
navigate to it using the Browse button. Once you have located the
replica, press OK to start the synchronization process.
Figure 10-12. The Synchronize Database dialog
A progress dialog will appear. If the synchronization process
completed successfully, a dialog will appear confirming this fact and
informing you that you need to close and reopen the database to see
all changes. Select Yes to let Access close and reopen the database.
10.2.2.3 Resolving conflicts
If multiple users have made updates to the same record in different
replicas, one or more users will be
informed of conflicts when they close and reopen the database to
complete synchronization. See Recipe 10.2.3 of this solution for
more details on how Access determines which change
"wins" a synchronization conflict.
If one or more of your edits
"loses" in the exchange, you will
see a dialog the next time you open the database, stating
"This member of the replica set has conflicts from
synchronizing changes with other members. Do you want to resolve
conflicts now?" To resolve the conflicts, follow
Choose Yes at the conflict dialog to start the resolution process.
A second dialog will appear, summarizing the conflicts that have
occurred (see Figure 10-13). Select a table in the
list box and press the View button to see the conflicts for that
Figure 10-13. The Microsoft Replication Conflict Viewer dialog
After a brief delay, a conflict resolution form will appear for the
table. A conflict resolution form for the tblCustomer table is shown
in Figure 10-14.
Figure 10-14. A conflict resolution form for tblCustomer
For each conflict record, the conflict winner will appear on the
lefthand side of the form and the conflict loser will appear on the
right. Pick the version of the record that you feel is more
you'd like, you can edit one version, combining data
from both versions or some third source of information. To resolve
the conflicting record, press either the Keep Winning Change button,
or the Resolve With This Data button. If you want to resolve the
conflict later, choose the Postpone Resolution button. Repeat the
process for each record in the conflict table.
Close the form and repeat Steps 2-4 for any remaining tables.
You will then need to propagate the changes to all the other replicas
in the replica set by choosing Tools Replication...
when you replicate a database in Access, you change the database
structure so that Access can track changes made to the database and
later synchronize those changes with other copies of the database.
Copies of a replicated database are called replicas; the original
master copy is called the design master. You can make design changes
only to the design master. The design master and its replicas make up
a replica set. You can synchronize only members of a replica set.
When converting a nonreplicated database to a replicated one, Access
makes the following changes:
Adds additional tables to track
Adds additional fields to each
table to ensure uniqueness of records across replicas and to track
Adds new properties to
Changes any sequentially
assigned AutoNumber fields to randomly assigned AutoNumber fields to
reduce the possibility of AutoNumber conflicts
When you synchronize replicas,
Access compares records in each replica using the hidden s_Generation
field to determine if records have been updated. During
synchronization, only changed rows are exchanged between replicas.
When conflicting edits are detected during a synchronization
exchange, Access determines which edited version of a record
"wins" an exchange using the
If a record in one replica was changed more times than in the other
replicas, it wins.
If all copies of a record were changed an equal number of times,
Access randomly picks a winner.
Only users with "losing" edits are
notified of conflicts.
Replication works best when your replicas
are only loosely coupled, and it isn't critical that
all changes be synchronized as soon as they are made. It is best to
replicate only tables, and not forms, reports, or other Access
objects. Although Access supports replicating other database objects,
it doesn't always work well. You may find that in
attempting to synchronize design changes, only partial changes are
propagated to the replicas, creating additional headaches. In
addition, Access replication is suitable only when you anticipate a
small or moderate number of updates to the same records in different
replicas. If you need real-time synchronization or if you anticipate
a high number of updates to the records across replicas (conflicts),
you may wish to consider using the replication services built into
server databases such as Microsoft SQL Server or some other