Database replication is an IT pro topic to its core. The basic idea behind replication is to permit multiple copies of a database to perform independently but make them respond as if they were identical copies of the same database. These copies can operate in various capacities. For example:
- Field representatives, such as sales representatives or technicians, can work with databases disconnected from a headquarters version.
- Databases with loads that tax the performance of a single server can run on multiple servers to accommodate more users.
- Sales, inventory, and production applications can reside on different computers in separate cities. Yet the databases for these applications can contain tables that can be synchronized to serve the overall needs of the enterprise.
- Database replicas can eliminate the need for a separate backup strategy. The database copies become ongoing backups of one another.
Database replication is a stable and mature feature with Access 2000 because this is the third version to support the product. Even from its debut, Access database replication sported exciting functionality that set it apart from other databases. Access is the only database that supports the replication of application objects, such as forms and reports, as well as data between database copies. Starting with Access 97, Microsoft supported heterogeneous replication by allowing SQL Server databases to synchronize with Access databases. Access 2000 builds on this feature. This advanced functionality is amazing when you remember that Access is a desktop database manager known for its ease of use and low-cost computing requirements.
In this article, we’ll introduce Access database replication. The topic is big enough to merit coverage in more than one article. Therefore, this article's goal is to equip you with a broad overview of the topic. Subsequent articles will build on this overview and examine selected subsets of replication functionality.
What does Access replication do and how does it work?
Access replication performs two basic functions. First, it copies data between pairs of databases. Second, it copies design changes between pairs of databases. These design changes can include updates to tables and queries as well as application objects, such as forms and reports.
Access replication can exchange data with all other database copies in the same replica set. Each database copy in a set is a replica. This kind of replication model is sometimes called a multimaster model since any, and potentially all, replicas can serve as a master database that passes its changes on to the remaining replicas in a set.
You start a replica set with a standard Access database file. When you make a database replicable, Access converts it by adding selected System and Hidden objects to the database. You can control the visibility of these fields through the Tools | Options menu. Select both the System Objects and Hidden Objects check boxes to expose the special replication fields and tables (or clear the check boxes to hide those objects). You don’t strictly need to view System and Hidden objects in order to use replication, but it can be helpful to examine them to understand how Access database replication works.
When passing database design changes between replicas, the rules are different from those for passing data. Only one replica, the Design Master, can serve as a source for passing design changes. If you add a new report to an application, you will first develop and test the report’s design and behavior on the Design Master. Then, you will synchronize all other replicas with the Design Master so that other replicas can receive the new report.
Typically, the Design Master originates as the database that Access converts from a regular database file into a replicable format. Any other global replica can serve as a Design Master. A global replica is a traditional replica, such as those available in Access 95 and Access 97 (Access 2000 added two new types of replicas). A replica set should have only one Design Master at a time.
The typology of replica sets can affect their performance. A typology refers to the physical layout and connections between replicas. Replica set typology affects how long it takes to propagate data and objects throughout a set because all synchronization is pair-wise. Recall that with traditional Access databases, it’s efficient to isolate data and design objects in two separate databases. If you follow this design as you incorporate replication into an application, you’ll likely have two or more independent replica sets. For example, you may have one replica set in which each replica contains a copy of an application’s tables. A second replica set can hold the application objects, such as forms, reports, data access pages, macros, and modules.
Suitable applications for Access replication
Access replication has four main distinguishing features. First, its replicas will not necessarily be identical at any moment. It can take several rounds of synchronization to disperse fully the data changes in all replicas throughout a replica set. Second, Access enables synchronization between occasionally disconnected replicas. The connection can be as simple as a dial-up Internet line. Third, Access replication is appropriate for those contexts that require the distribution of application objects, such as forms and reports. Fourth, Access replication brings a sophisticated data management tool within the reach of more applications because it runs on inexpensive hardware and it’s very easy to administer Access databases.
Load balancing is an Access replication advantage that tackles one of Access' traditional weaknesses. Access applications don’t scale well as you add more than about a score of users. Two leading factors contributing to this problem are its file-server architecture and its inability to scale up performance as a database server adds processors. By building multiple replicas that run on two or more servers, Access applications can scale up to serve more than a score of users.
Access replication lends itself well to situations that require disconnected computers. One likely implementation of this involves traveling sales representatives. Before leaving for a trip, the salespeople can synchronize their replicas with the headquarters version to copy the most recent product and price lists. After returning to headquarters, salespeople can synchronize again to copy new orders and expenses to the headquarters database. Since Access supports Internet replication, employees in the field can synchronize with the headquarters database even before they return to headquarters. This ability can improve both the accuracy and timeliness of data flow throughout an organization.
The capability to expedite data flow for workers using disconnected computers also serves branch offices. When connection line costs are important, the ability of Access to work with standard dial-up lines (or ISDN lines) over the Internet can drive down an application’s costs to the point of making it feasible. The attractiveness of Access replication for branch office applications goes beyond costs too. Access is a desktop database manager that many people in an organization are likely to know. This can smooth the way for application deployment and empower non-IT personnel to perform elementary data management tasks in branch offices.
Finally, Access replication supports the remote deployment of application updates because you can synchronize application objects and data changes across a replica set. This feature is particularly valuable to IT departments that need to deploy application updates over the life cycle of an application.
What's new with Access 2000 database replication?
Microsoft has continued to refine database replication with Access 2000 in a host of new ways. This section briefly summarizes a selected set of enhancements, including:
- Column-level tracking
- Priority-based conflict resolution
- Replica visibility
- Jet and Replication Objects (JRO)
- Bidirectional replication with SQL Server
When you design your applications to take advantage of the multimaster model for adding, editing, and deleting records, the new column-level tracking feature can add significantly to an application’s ease of use. Earlier versions of Access tracked changes at the row level. Therefore, if two different replicas each update the same record, Access recorded a conflict—even if the changes were to different fields. Conflict resolution is time-consuming. This is especially true if there’s no conflict in the first place. The new column-level tracking feature avoids setting a conflict when two replicas update different fields of the same record.
The way earlier versions of Access resolved conflicts was confusing to some replication users. Access chose a winner based on whichever replica changed a record the most. Starting with Access 2000, replica priorities determine the winner in a conflict. When Access creates a replica, it assigns a priority that you can programmatically override through either the user interface or a program. A replica’s priority is read-only except at the time of the replica’s creation. When there’s a conflict between two replicas with the same priority, Access selects as the winning replica the one with the lower ReplicaId, which is a read-only property that Access assigns uniquely to each replica in a replica set.
Replica visibility is another innovation introduced with Access 2000. Before this version, all replicas were of the same type. The new version, however, creates replicas with any of three visibilities. Global visibility replicas are like those from previous Access versions. They can synchronize with any other replica, and you can schedule synchronization cycles for them with Replication Manager, a Microsoft Office Developer component.
Replicas with either local or anonymous visibility have a narrower role in Access database replication than global replicas. Local visibility replicas can synchronize only with their parent, which must be a global replica. Local replicas cannot synchronize directly with one another. However, you can use Replication Manager to schedule synchronization cycles for local replicas. When you use a single global replica with two or more local replicas, you can enforce a hub typology.
Replicas with anonymous visibility specifically target synchronization over a Web. Like local replicas, these replicas always have a global replica as their parent, and they can synchronize only with that parent. However, anonymous replicas have less effect on the replica set and offer more limited functionality. For example, all synchronization cycles for these replicas must originate with the anonymous replica or its parent. You cannot use Replication Manager to schedule a synchronization cycle with an anonymous replica.
Access 2000 supports two routes for programmatically controlling replication. First, you can use Data Access Objects (DAO). If you use this approach, you will not be able to control selected new features, such as replica visibility and priority settings. However, the DAO interface is convenient for those already programming replication with DAO from prior versions.
The other major programmatic approach to controlling replication is the Jet and Replication Objects Library (JRO). JRO has just three objects: the Replica object, the Filter object, and the Filters collection. Replica objects have properties and methods that explicitly support replication. Visibility and priority values are properties of the Replica object in JRO. Filter objects enable the specification of criteria for the subsets of a database that populate a partial replica. Partial replicas can expedite synchronization for field representatives and branch offices.
IT pros are likely to gain significant benefit from the ability of Access 2000 to have its replicas synchronize bidirectionally with SQL Server. Access 97 enabled unidirectional synchronization from SQL Server to Access databases, and Access 95 offered no interoperability between Access replicas and SQL Server. When a SQL Server replica participates with Access replicas in a replica set, the SQL Server replica must act as the hub. The Access replicas are only able to synchronize with one another through the SQL Server replica at the hub.
Tools for implementing Access replication
There are five tools for controlling Access database replication. These approaches complement one another because of their optimization for different application environments. The five tools are the Windows 95/98/NT 4 Briefcase, the Access menu, DAO, JRO, and Replication Manager. Replication Manager is available exclusively with Microsoft Office 2000 Developer Edition.
Briefcase Replication is appropriate for a developer or database administrator who needs to take a copy of a database for additional modifications on the road or at night. Just drag either a normal database file or a replica to the My Briefcase folder on the Windows desktop of a laptop computer. The replica in the My Briefcase folder can be modified in the normal ways. When you return to the office, you can update the replicas in the My Briefcase folder and its parent with changes made to either database copy. Just right-click the replica in the Briefcase and choose Update to launch synchronization.
You can use Briefcase Replication with databases that are not yet replicable and replicas in an existing replica set. If the database copy is not already replicable, dragging it to the My Briefcase folder will make it replicable. The process gives you the option of making a backup copy of a database file. Always take this option since making a database replicable can increase its size considerably, and there’s no automatic way to recover the initial version. You can also choose to make the replica in the My Briefcase folder the Design Master. If you need to make design changes, such as adding new fields to a table or creating new queries to a database, choose to make the Briefcase replica a Design Master.
The Access menu commands provide a richer set of features for creating and managing a replica set. Choose Tools | Replication to view the menu commands available for administering a replica set. The Create Replica command will either make a database replicable or add a new replica to an existing replica set. The Synchronize Now command allows you to choose any other replica in a replica set as a synchronization partner. The Update command in Briefcase Replication supports synchronization exclusively with the parent of the Briefcase replica. In addition, synchronizing with the menu command will automatically prompt you to resolve conflicts if any occur, but the Update command does not warn about conflicts or offer an opportunity to resolve them. The other Access menu commands are richer. Creating a replica with the Access menu commands permits the setting of a priority value for a replica and enables the creation of partial replicas. The menu commands also simplify the creation of replicas that prevent the deleting of records by users.
When you select the Create Replica command, the Location Of New Replica dialog box appears. Select the Prevent Deletes check box to create a replica from which users cannot delete records. Click the Priority button to override the default priority value that Access assigns to a replica. Priorities can assume values in the range of 0 through 100. By default, the Design Master receives a priority value of 90, and a child replica has a priority that is 90 percent of its parent’s priority value.
The JRO model is generally richer than the DAO model for administering replication. In particular, JRO supports replica priority and visibility properties, but DAO does not. However, DAO offers access to database properties while JRO does not. One database property tracks whether forms, reports, data access pages, macros, and modules referenced in a Design Master are replicable. This property, ReplicateProject, assumes Boolean True or False values. The procedure in Listing A shows a function procedure that returns a value of True or False depending on the value of a database’s ReplicateProject setting. You can use this procedure with a Design Master that you inherited from another application designer to determine if application object changes are replicable.
'Create a Reference to the DAO 3.6 Object Library
Function ProjectReplicable() As Boolean
Dim db As DAO.Database
Dim p As DAO.Property
Dim dbProperties As DAO.Properties
'Set object pointers
Set db = CurrentDb
Set dbProperties = db.Containers!Databases. _
'Enumerate for database's ReplicateProject property
For Each p In dbProperties
If p.Name = "ReplicateProject" And _
p.Value = True Then
ProjectReplicable = True
Replication Manager provides a collection of benefits that target database systems administrators. First, it offers a graphical user interface that’s particularly useful for visually depicting the typology of a replica set. In addition to showing the typology, you can actually use it to manage the replica set. Second, you can schedule synchronizations on a regular cycle with Replication Manager. This automatically keeps replicas synchronized without individual replica users initiating a Synchronize Now command. Third, Replication Manager enables indirect synchronization so that Access can drop off the changes for a replica that is not physically connected to a network. Later, when a laptop connects to a network, it can receive the changes. Fourth, Replication Manager provides configuration support for synchronization over the Internet or a corporate intranet.
This article introduced the basics of database replication with Access 2000. Subsequent articles will give the details on how to create and manage replica sets with the Access commands, JRO coding techniques, and Replication Manager.
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.