Database replication is a sophisticated, enterprise-oriented technology. Access 2000 unlocks the core benefits of this technology for IT pros with a simplicity that underscores its raw power and ease of use. The Access 2000 menu commands give you enough capability to deploy a replication solution quickly and easily. Replica administrators require only Access power user skills when they use the built-in commands. The low level of administration skills, combined with the minimum hardware and communication costs for Access 2000, make implementing and managing replication solutions attractive for large, dispersed organizations with limited resources or staff. Access 2000 replication may be the technology that helps you do more with less.
In this article, we’ll describe two routes to realizing the benefits of the Replication menu commands in Access 2000. We’ll begin with a brief summary of the five commands and an examination of selected features of those commands. Then, we’ll demonstrate the use of the commands with a simple perpetual inventory system that serves three departments.
There are no prerequisites for reading and understanding this article. However, if you find the terminology or functionality of Access replication unclear, consider reading "An introduction to Access 2000 database replication ." You can learn the basics of Access database replication as well as the new features that exist in Access 2000.
Replication menu command summary
Access 2000 offers five Replication menu commands. Select Tools | Replication to see these command names:
- Create Replica
- Synchronize Now
- Resolve Conflicts
- Partial Replica Wizard
- Recover Design Master
Typically, you would start building a replication project by invoking the Create Replica command from a normal Access database file. This command can perform either of two functions. First, when you initially issue Create Replica within a normal database file, it makes the file replicable. This beginning application of the command also builds a second replica. These two replicas comprise a replica set. Second, when you invoke the Create Replica command from either of the initial two replicas (or a child replica to either of these), the command creates a single new replica.
The initial application of the Create Replica command to a database file makes the file replicable by adding special System fields to each table and a collection of tables. Access 2000 orchestrates replication through these objects. Choose Tools | Options and select the System Objects check box on the View tab of the Options dialog box to view the special replication system objects. You do not have to view or know about these fields and tables to use Access database replication, but they can profoundly affect the file size of a database. For example, making the sample Northwind database replicable nearly doubles its size from about 2 MB to 4 MB! Adding more replicas to a replica set and performing synchronizations can further enlarge storage requirements relative to the original database file. Microsoft provides no automatic means for retrieving the initial database. For these reasons, you will ALWAYS want to take advantage of the opportunity that the Create Replica command offers to back up your original database.
As different groups of users work with the replicas in a replica set, the replica contents can diverge. You use the Synchronize Now command to update the data changes in both replicas. Synchronization occurs exclusively between pairs of replicas. After synchronization, the two replicas map perfectly to one another again. However, the cost of this regained consistency is the loss of incompatible changes. Access 2000 picks a winner between conflicting data changes based on the priority property setting of each replica. Access 2000 treats synchronization errors just like data conflicts. A synchronization error results from changes to one replica that are incompatible with the design of another, such as a primary key for a record in one replica that is a duplicate for a different record in another. With Access 2000, you can resolve data conflicts and synchronization errors with a unified Conflict Resolver.
Access replication follows a multimaster model for data changes in which data modifications to any replica can propagate to those of all other replicas. Data changes refer broadly to additions, revisions, and deletions. However, for database design changes such as new queries, forms, or reports, changes propagate only from the Design Master to other members of a replica set. To add a new query from the Design Master to the replica set, you must synchronize each replica with the Design Master.
After a synchronization that results in data conflicts or synchronization errors, Access 2000 prompts the database user to resolve the conflict(s). If a user declines, the user can subsequently address the conflicts with the Resolve Conflicts command. Once you issue this command, you’ll see the Conflict Viewer dialog box. Selecting a table opens the conflicts in that table for viewing. The Conflict Viewer dialog box allows you to keep the winning or losing change or to revise either and keep the updated result. Because of the significant changes you can effect with this process, conflict resolution should be restricted to power users and database administrators.
The Partial Replica Wizard command targets the creation of replicas for field representatives and branch offices that do not need a copy of all the records in a database. Since a partial replica contains a subset of the records in a database, synchronization can go faster. This feature is especially significant for individuals who will communicate with a database via dial-up lines or branch offices that will link to a headquarters version of a database at less than conventional LAN speeds.
The Recover Design Master command explains the process for making a new Design Master in a replica set. You should use this process only when the old Design Master becomes corrupted or is otherwise unavailable. Before converting a normal global replica to a Design Master, synchronize the replica with all other replicas in a replica set. Then, invoke the Recover Design Master command. Click past the instructions and choose Yes to make the current replica the new Design Master.
The sample perpetual inventory application
The basic idea behind a perpetual inventory system is that new productions automatically add to inventory while sales automatically subtract from inventory. The example we’ll use targets an application for three different departments: Manufacturing, Sales, and Administration. Instead of implementing the system as a single database file accessed by workers from all three departments, our example deploys separate replicas to each department. In this section, we’ll describe the operation of the initial database file, and we’ll highlight some advantages of deploying the solution as three replicas instead of one multiuser database file.
Deploying this solution as three separate replicas instead of one multiuser database file allows departments better accessibility to its replica while reducing network traffic. In particular, the Administration department can process its most recently available new production and sales even while the Manufacturing and Sales departments are simultaneously entering new production and sales data.
To implement our replication solution, we need three replicas—one for each department. Start the process by choosing Tools | Replication | Create Replica from the initial perpetual inventory system file. When you start to convert a normal database into a replica, the Create Replica command will be the only option enabled on the menu. Before Access 2000 generates the replica, it will give you two prompts. You can back out of the process at either step. The first prompt asks if you want to close the database and make the replica. You should choose Yes if you want to proceed. A second prompt gives you a chance to make a backup of your original database file. If you want to abort the process, choose Cancel in response to the prompt offering to make a backup. Choosing Yes advances the process and creates a backup file in the same folder as the initial database file. The backup file receives the same name as the initial one, but its extension is .bak.
In our example, the initial database file has the name AdminReplica.mdb and is used by the Administration department. Access 2000 assigns the name AdminReplica.bak to the backup file. The Create Replica command automatically renames the replicable version of the database file with the same name as the original database file. For this reason, you should assign a name to your initial database file that reflects its ultimate use.
After making the initial database file replicable, Access automatically prompts you for the location of a new replica. The dialog box can accomplish up to four functions for the second replica. You can designate:
- A path and name to the replica. By default, Access names the second replica "Replica of" along with the original filename. Since this replica is for the Manufacturing department, we rename the file ManReplica. Access 2000 automatically assigns the .mdb extension because the replica is an Access database file.
- A replica's visibility property with the Save As Type drop-down box. The default setting is global, but the example uses local. This difference exists because the replicas for the Manufacturing and Sales departments do not need to synchronize with each other; they only need to synchronize with the Design Master replica for the Administration department.
- A priority value for the replica. Access assigns by default a priority of 90 to the Design Master. When you select either local or anonymous visibility for a replica, its priority must be 0. If the visibility of the second replica were global, its default value would be 90 percent of the Design Master's priority. However, you can override this default priority by clicking the Priority button and assigning a new value from 0 through 100.
- The second replica as one that does not permit record deletions by users. Select the Prevent Deletes check box to enforce this rule within a replica.
Our example requires one more replica for the Sales department. From the Design Master, select Tools | Replication | Create Replica. In the resulting dialog box, give the new replica a name of SalesReplica and set its visibility to local.
The operation of the perpetual inventory system requires two rounds of synchronization to exchange an updated inventory status throughout the replica set completely. First, AdminReplica must synchronize with ManReplica and SalesReplica. In this first synchronization round, AdminReplica collects the latest production and sales data. Second, AdminReplica runs its routines for adding new production to inventory and subtracting new sales from inventory. Third, AdminReplica launches another round of synchronization with both ManReplica and SalesReplica. In these synchronizations, AdminReplica passes back the updated inventory positions to the two local replicas in its replica set.
To launch this process, open AdminReplica. Neither ManReplica nor SalesReplica needs to be open, but AdminReplica needs to have a live network connection to both files (which means that the computers for those replicas must be running and connected to the network). Choose Tools | Replication | Synchronize Now from AdminReplica. Select either the ManReplica or the SalesReplica from the Directly With Replica drop-down list box. If you don’t see the replica member with which you want to synchronize, click the Browse button to navigate to that replica member. This will select the target replica and add it to the drop-down list for future reference. After selecting the target replica, click OK to begin synchronization. Repeat the process for synchronizing AdminReplica with the other member of the replica set.
Run the inventory administration procedures after synchronizing AdminReplica with both ManReplica and SalesReplica. Then, with the Synchronize Now command, re-synchronize AdminReplica with the other two members of the replica set.
Indirect replication is possible when Replication Manager previously configures replicas for this function. With indirect synchronization, you can pass along changes to a replica that is not currently connected to the network, such as one residing on a laptop or one on a computer that is temporarily out of operation. Replication Manager is available exclusively with the Microsoft Office 2000 Developer edition. We’ll examine indirect synchronization in a future article.
The synchronization process legitimately creates some conflicts. For example, synchronizing AdminReplica with ManReplica causes data conflicts. Furthermore, the automatic Access 2000 rules for picking conflict winners doesn’t work for the application. Since ManReplica has a priority of 0, its new production values lose to the zeroed values for new production in AdminReplica, which has a priority of 90. However, the application wants new production values from ManReplica to update the zeroed values in AdminReplica.
After the synchronization, the database administrator for AdminReplica can choose Tools | Replication | Resolve Conflicts to edit the conflict winners. Then, the administrator should choose to view conflicts in tblNewProduction. This opens the Replication Conflict Viewer dialog box. In this scenario, select the Overwrite With Conflicting Data radio button and click Resolve. Repeat this task for each losing record from ManReplica. When all the conflicts are resolved in this way, the new production values appear in AdminReplica's tblNewProduction.
There are several approaches for circumventing or automatically resolving these conflicts. One involves redesigning the perpetual inventory system so that it adds new production records instead of rewriting the values from zero to a non-zero value. Other solutions involve programming replication so that it tailors itself to the application better. We’ll examine custom programmatic solutions in the next article on Access database replication. However, even without any programming for either retrofitting an old application or adapting the standard replication features, you can create and administer sophisticated multiuser, multidatabase solutions!
Rick Dobson, Ph.D., and his wife are development and training consultants. He is the author of the best-selling book Programming Microsoft Access 2000 for Microsoft Press. Rick is a regular contributor to TechRepublic as well as numerous other computer periodicals. In addition, he presented training sessions and seminars on Access and Web development topics in Australia, Canada, the United Kingdom, and throughout the United States. Rick is a Microsoft Certified Professional and a Microsoft Certified Trainer. You can reach Rick at either of the two Web sites that his practice maintains (www.programmingmsaccess.com and www.cabinc.net ).The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.