Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

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:

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 10-02.BAK.
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

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

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

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

[ Team LiB ]