Database replication is the process of converting your database to a new version, called a Design Master, and then creating replicas of that master database that can be distributed to different people, who can then edit the data. Each person working on the database must have his or her own replica.
When you create a replica with the Create Replica command, Access closes the database and creates a master and a replica (named Replica of <database>). You repeat this for every replica you want to create. The name of each replica is made unique by the addition of a number. After users have made changes to their replicas, the modified replicas are returned to you, and you use the other commands on the Replication submenu to synchronize the versions and resolve conflicts. For more information on this process, search on replication in Access online Help.
Database synchronization is the process of comparing the information between two members of the replica set (two versions of the master database) and merging any changes. If changes to the same field in the same record cause any conflicts, a winner and a loser are determined by priorities assigned to the members of the set. (For example, the replica that was edited by The Garden Company’s sales manager would probably have a higher priority than the one edited by a sales clerk.) The winning change is applied to the master database, and the losing change is recorded as a conflict. After the master database has been compared to each replica, all changes have been recorded, and all conflicts have been resolved (you can use the Conflict Resolution Wizard to help with this process), all the replicas are updated with the current information from the Design Master and are sent back to the remote locations.
Tip
This process sounds complex, and it is. If you think you need replication, you should look into acquiring Microsoft Office XP Developer (MOD). Several of the more difficult tasks are made easier with the help of the Replication Manager, which is included with MOD.
The primary use of replication is in data warehouses where, for example, daily database updates might be sent from branch stores to be synchronized with a master database at night. Updated information about stock levels and specials would then be returned to the branch stores in the morning.
A full-scale exercise demonstrating database replication is too complex for the simple format of this book, but one fairly simple form of database replication can be useful to the average user. Microsoft Windows comes with Briefcase, which uses replication to keep files in sync when you work on different computers in different locations. For example, the owner of The Garden Company might want to take the GardenCo database home at night to work with on her laptop.
In this exercise, you will replicate a database to the Briefcase folder on the desktop of your computer. (It is assumed that you don’t currently have a Briefcase folder on your computer.)
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSSecureReplicate folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
Right-click the desktop, point to New, and then click Briefcase on the shortcut menu.
A New Briefcase icon appears on your desktop.
Rename New Briefcase
SBS Briefcaseby clicking the icon, clicking its name, replacing New withSBS, and pressing
.Click the Windows Start button, and navigate to the My DocumentsMicrosoft PressAccess 2003 SBSSecureReplicate folder.
Reduce the size of the window, and position it so that you can see both the GardenCo database file and SBS Briefcase on your desktop.
Drag GardenCo to SBS Briefcase.
The Updating Briefcase alert box displays the message Copying from ’Replicate’ to ’SBS Briefcase’, and after a moment, a message appears.
Click Yes to continue.
Click Yes to have Briefcase make a backup copy of your database.
A dialog box appears, informing you that Briefcase has converted your database to a Design Master and placed a replica in the SBS Briefcase folder.
Click OK to accept the option to allow design changes only in the original copy of the database and to finish the replication process.
Navigate to the practice file folder (which should still be open), and notice the difference in size between the Design Master (GardenCo) and the backup (GardenCo.bak). Replication substantially increases the size of the original database.
The Design Master and a backup copy of the database are in the practice file folder, and a replica of the database is in the SBS Briefcase folder. If you want to work on the database on a different computer that is not connected to this one through a LAN, you can copy the replica (or the entire SBS Briefcase folder) to removable media, such as a Zip disk or CD-ROM. (It’s almost certainly too big for a floppy disk.)
To simulate the editing and synchronizing process, start by double-clicking the SBS Briefcase folder to open it.
In the Welcome to the Windows Briefcase dialog box, click Finish.
The Briefcase is similar to a normal Windows folder. Notice that the Sync Copy In column has a path to the Design Master, and the setting in the Status column is Up-to-date.
Double-click GardenCo to start Access, and to open the database.
A replication symbol appears to the left of each table, form, and other object name. Note that design changes can be made only in the original file.
On the Objects bar, click Forms, and then double-click Products to open the Products form.
Change the name of the first product from Magic Lily to
Mystic Lily.Close the form and the database.
Quit Access, and then close the SBS Briefcase window.
If you were working on a different computer, at this point you would be ready to synchronize the replica with the master database stored on the office computer.
On your desktop, double-click the SBS Briefcase folder.
The setting in the Status column has changed to Needs updating.
On the Briefcase menu, click Update All.
The Update SBS Briefcase dialog box appears, showing the condition of both databases. Because only the replica has changed, an arrow pointing toward the master database suggests that the changes in the replica replace the information in the master. If only the master had changed, or if both databases had changed, the arrows would be different. You can accept the suggestion, or you can right-click an entry and select another option to override the suggestion.
Click Update to update the master database.
A status message appears, indicating the update is being performed. When it is finished, press
to update the folder to reflect the Up-to-date status.Start Access, navigate to the My DocumentsMicrosoft PressAccess 2003 SBSSecureReplicate folder, double-click GardenCo, and acknowledge the safety warning, if necessary.
Open the Products form, and confirm that the change you just made appears there.
Close the Products form.
CLOSE the GardenCo database.