Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 10.2 Maintain Multiple Synchronized Copies of the Same Database

10.2.1 Problem

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?

10.2.2 Solution

Access
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 may arise.

10.2.2.1 Replicating a database

The steps for replicating a database using the Access menus are as
follows:

1. Back up the database and safely
   store the backup.

2. 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
   

   10-02.BAK .

3. You
   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

4. 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

5. 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.

6. 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 replicasfor
example, when a salesperson returns to the office and plugs his
laptop into the office networkfollow these steps:

1. Start Access and open any of the replicas in the replica set.

2. Select Tools Replication...
   Synchronize Now.

3. 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

4. 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
these steps:

1. Choose Yes at the conflict dialog to start the resolution process.

2. 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
   table.

Figure 10-13. The Microsoft Replication Conflict Viewer dialog

3. 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

4. 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
   "correct." If
   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.

5. Close the form and repeat Steps 2-4 for any remaining tables.

6. You will then need to propagate the changes to all the other replicas
   in the replica set by choosing Tools Replication...
   Synchronize Now.

10.2.3 Discussion

To summarize,
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
  changes

• Adds additional fields to each
  table to ensure uniqueness of records across replicas and to track
  changes

• Adds new properties to
  the database

• 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
following rules:

• 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
system.