Rick Dobson
Replication is by far the most exciting addition to Access 95. Rick provides an introduction to Access replication, including DAO code samples for creating and managing replica sets.
Access 95 is the only desktop database manager to offer replication. With replication, Access applications can serve much larger pools of users. By exchanging changes between database replicas, Access replication reduces network traffic. Replication also targets users who are intermittently linked to a network, but who require the bi-directional flow of data. With the ability to exchange database objects, such as forms, reports, and modules, as well as records, Access replication makes it as easy to propagate design changes as it is to exchange data among an application's users.
Many data sharing situations can be served by Access replication. Mobile workers can exchange data with a central office. By giving departments or users their own replicas, you can spread the data processing load across several computers and reduce the potential for multiuser locking errors. Because Access supports the replication of both data changes and design changes, you can also use it to roll out application updates and perform dynamic backups of databases. By comparison, neither Microsoft SQL Server 6 nor Oracle 7 support the propagation of design changes for database objects, such as forms and reports. Oracle does support limited design changes for indexes and triggers, however.
Access replication builds on several core constructs. The replica is a special database equipped to automate the exchange of data revisions with other replicas in its set. You can have many replicas in a replica set, but you can have only one design master. This special replica is the only one to which you can make design changes. Synchronization is the process by which you exchange updates between replicas. Data revisions occur at the record level. If two users change the same record in different replicas, their changes can conflict. Conflict resolution is the process of automatically declaring a winner and permitting further resolution later.
There are four avenues for creating and managing replica sets. First, the Windows 95 Briefcase offers drag-and-drop support for personal replication needs. Second, the Tools, Replication menu commands offer a more granular level of control. Third, DAO programming lets developers provide custom replication solutions. You can use the replication methods and properties to manage the way replication performs in an application. Finally, Replication Manager, which ships with the Access 95 Developer's Toolkit, targets large, networked replication environments. It also provides a graphical interface for implementing regularly scheduled synchronizations.
When you convert a database into a replicated design master, Access adds new fields and tables to the database. In addition, Access changes the function of AutoNumber fields and adds new properties to a database. Some of these changes can profoundly impact the size of a database. For example, I converted the Northwind database into a design master replica, which increased the database size from 2M to 3.4M—a 70 percent gain!
Tables in a database converted to a replica gain at least three new system fields:
The s_GUID field uniquely identifies each record in a replica set. The s_Generation field tracks the number of changes to a record in a replica since the last synchronization. s_Lineage is a binary field that stores information about the history of changes to a record. In addition, each Memo or OLE Object field adds one more field that tracks changes to them. These additional replication fields will reduce the maximum number of bytes per record and the number of user fields you can have in a table.
Replication also adds many new system tables that are used by Access to manage replication. Access also creates temporary conflict tables, one for each replicable table in the database with outstanding conflicts. These tables store conflict information after a synchronization and are named by appending "_Conflict" to the end of the table name. For example, the conflict table for tblCategories would be tblCategories_Conflicts.
When you replicate a database, Access generates random numbers for all AutoNumber fields (called counter fields in prior versions of Access) when you add new records. If your application requires sequential values in these fields, consider using a Date/Time field instead.
There are two user-level routes for creating and managing replicas: the Access UI and the Windows 95 Briefcase.
You use the Create Replica command of the Tools, Replication menu to create a new replica set. All other Tools, Replication options remain grayed until you create a replica. Before creating a design master and the first replica, Access asks if you want it to create a backup copy of your original database. I strongly urge you to accept this option because Access doesn't provide an easy route for restoring a replica to its unreplicated state.
After you've created a replica set, the Synchronize Now and Resolve Conflicts menu commands are enabled. You use Synchronize Now to exchange updates with another replica. You select a replica with which to synchronize using the Synchronize With combo box in the Synchronize Database dialog.
During synchronization, Access exchanges data revisions between the two replicas. When users have changed the same record in two replicas, Access declares whichever replica revised the record the most as the winner. The losing revision is written to the conflict table in the losing replica.
The Access UI offers three opportunities to override the default conflict resolution. Immediately after synchronization, Access asks if you wish to resolve conflicts. If you decline the option, you can choose the Resolve Conflicts option from the Tools, Replication menu at any time during a session. If you don't resolve conflicts during a session, Access prompts you to resolve them the next time you open the replica. In all three cases, if you choose to resolve conflicts, Access starts the Conflict Resolution Wizard (see Figure 1).
Figure 1. The Conflict Resolution Wizard prompting the user to keep or overwrite an update.
When a design master becomes damaged or deleted, you won't be able to propagate design changes throughout your replica set until you designate a new design master. If this happens, you need to synchronize the replica that you plan to convert to a design master with all other replicas in the set and choose Recover Design Master from the Tools, Replication menu to designate a new design master.
Never create a second design master in a replica set. Creating two functioning design masters can partition your replica set into two parts and cause data loss.
The Access menus offer more functionality and are available to Windows NT as well as Windows 95 users, but the Windows 95 Briefcase is particularly attractive for Windows 95 users with personal needs, such as replicating a database for a trip home.
The Windows 95 Briefcase facilitates the automatic copying of files for mobile workers and those who occasionally bring computer assignments home. Access enriches the Briefcase over other Windows 95 applications so that it can perform bidirectional synchronization of databases at the record level. Without Access replication, the Briefcase copies the most recently changed file to its counterpart.
You can make a replica with the Windows 95 Briefcase by dragging and dropping a database file from a computer in the Network Neighborhood into a laptop computer's Briefcase. If the database isn't already a replica, this converts the database into a design master and places a replica of it in the Briefcase. Although Briefcase will by default make the network version the design master, it presents a dialog box to let you select the Briefcase copy as the design master instead (see Figure 2). When you return to the office and reconnect the laptop to the network, select the database in the laptop Briefcase and choose Update Selection from the Briefcase menu. The Briefcase sends and receives updates between the laptop and the desktop replicas.
Figure 2. The Briefcase replication dialog box asks which copy to make the design master.
There are eight DAO replication methods and properties (see Table 1). You use these methods and properties to convert databases for replication and to manage the replica set.
Table 1. DAO replication methods and properties.
| Property or Method | Applies To | Purpose |
| MakeReplica method | Databases | Makes a new replica of a database; special constant allows the creation of read-only replicas. |
| Synchronize method | Databases | Synchronizes two databases; special constants let you control the flow of data out of, into, or bidirectionally between databases. |
| ConflictTable property | Tabledefs | Read-only property that returns the name of table holding conflict records after a synchronization. |
| DesignMasterID | Databases | A 16-byte GUID uniquely identifies the design master for the replica set. |
| KeepLocal property | Tabledefs, Querydefs Documents |
A value that indicates that, an object doesn't transmit its changes to other replicas in a set; can be set only before the database has been replicated. |
| Replicable property | Databases, Tabledefs, Querydefs, Documents |
A value indicating whether a database or its objects can be propagated across a replica set. |
| ReplicaID property | Databases | A 16-byte GUID that uniquely identifies each replica in a replica set. |
| ReplicationConflict- Function property |
Documents | The function name for a procedure that implements a custom conflict resolution rule. |
You convert a database for replication by setting its Replicable property. After you set this property to "T," a database or one of its document objects will be replicable. While you can't change the Replicable property from "T" for a database, you can set it to "F" for objects within a database. As I mentioned previously, it's a good idea to backup the database before replicating it.
You can instruct Access to not replicate certain objects—prior to replicating the database—by setting the KeepLocal property. You can use this property to help protect sensitive data and control replica size. The KeepLocal property can't be set once a database has been replicated.
If a table is in a relationship with another, both must have their KeepLocal property set to "T" or "F"—a local table can't be related to a replicated table.
All database objects created after the initial conversion are local by default. If you need to share a database object with the other replicas, import it to the design master if it isn't already there and set its Replicable property to "T."
When using the KeepLocal and Replicable properties with tabledefs, querydefs, and documents, you must invoke the properties initially with the CreateProperty and Append methods. First, create the property. Second, append it to the properties collection for the tabledef or querydef. You can declare their data type and value with the CreateProperty method. After initializing these properties, refer to them as any other property.
After you convert a database from a non-replicable to a replicable format, you'll want to populate its replica set by creating additional replicas. The original converted database becomes the design master by default. Additional replicas can either transmit and receive changes or just receive updates from other replicas.
After a database is replicable, you create additional replicas with the MakeReplica method. You must initially use the MakeReplica method on the design master. After you have at least one other replica, you can use MakeReplica with either a design master or an ordinary replica. When you make a new replica, Access copies all the replicable objects and their properties from the source to the target replica. Depending on the location of the new replica, any linked tables may or may not still be valid. You should check their operation, and correct them if necessary.
The ReplicaID and DesignMasterID properties are read-only. These two properties contain Globally Unique IDs (GUID) that uniquely identify the current replica and the design master for the replica set.
Microsoft asserts that these 16-byte numbers are guaranteed to be unique in the world across space and time.
If you wish to transfer design master status from one replica to another, you must re-assign the value of the DesignMasterID from the former design master ReplicaID to the new one.
You use the Synchronize method to synchronize a source replica with a target replica. An optional exchange parameter permits you to specify the type of synchronization. If you set this parameter to dbRepExportChanges, Access will export updates from the source replica to the target replica. If you use dbRepImportChanges, Access imports updates from the target replica to the source replica. If you use dbRepImpExpChanges, or fail to set this parameter, Access will perform a bidirectional transfer between the two replicas.
During synchronization, conflicts occur between replicas when each changes the same record. Even if the replicas change different fields, there is still a conflict because Access performs replication at the record level. The conflict resolver reconciles the changes between replicas according to which replica changed a record the most. The conflict resolver is invoked by the Synchronization method. The losing changes are recorded in conflict tables in the replicas in which they lose.
The ConflictTable property of a table returns the name of a table holding the losing records for synchronizations. The ReplicationConflictFunction property permits you to programmatically override the default conflict resolver with your own custom resolver. The default resolver still functions, but your application overrides it when users choose Resolve Conflicts or perform some other action that invokes your custom conflict resolution code. The ReplicationConflictFunction should be set to a string that literally spells the name of your custom resolver procedure.
You need to add the ReplicationConflictFunction property with the CreateProperty and Append methods. If you want to restore the default Resolve Conflict function, delete the ReplicationConflictFunction property from the database's properties collection.
I've created several examples that demonstrate DAO replication programming that are included in the sample database on the Companion Disk.
Before you can use replication, you must make a database replicable by setting a database's replicable property. In the sample database, you'll find a simple form, frmReplicateAnyDB, that gathers the information necessary to make a database replicable. It includes two text boxes: one for the path name and another for the filename of the database. End the path name with a back slash.
Click the button labeled Replicate Filename to invoke the cmdReplicator_Click procedure. This procedure concatenates the path name and filename to designate a database. Then, it calls the SetReplicable procedure.
The SetReplicable procedure follows. It takes as its argument the concatenated path name and filename. The procedure includes only essential error trapping, but you can easily enhance this to capture other possible user errors, such as references to nonexistent files:
Public Sub SetReplicable(strTargetDB)
On Error GoTo SetReplicable_Err
Dim db As Database
Set db = DBEngine(0). _
OpenDatabase(strTargetDB, True)
db.Properties.Append db.CreateProperty _
("Replicable", dbText, "T")
SetReplicable_Done:
db.Close
Exit Sub
SetReplicable_Err:
Select Case Err
Case 3367
MsgBox "Already replicable."
Case Else
MsgBox Err & " -- unexpected error"
End Select
Resume SetReplicable_Done
End Sub
After the On Error statement and after specifying variables for the procedure, the routine opens the database passed to it. The line after the OpenDatabase method creates the replicable property, sets it to "T", and appends it to the database's properties collection. If a user tries to make a database replicable that is already replicable, this will cause a 3367 error, which you trap and handle.
The code attached to the frmReplicateAnyDB form unconditionally converts a database and all its objects to be replicable. However, security or replica size may dictate a need for a more restricted set of replicable objects. By setting the KeepLocal property to "T" on selected database objects, you avoid the possibility of their replication.
For example, you might create the following event procedure to set the KeepLocal property for several documents in the Northwind database:
Private Sub cmdKeepLocal_Click()
On Error GoTo cmdKeepLocal_Click_Error
Dim dbs As Database
Dim doc As Document, prp As Property
Set dbs = CurrentDb
'Set container for object,
'Then, create and append property
'Repeat for other objects as necessary
Set doc = dbs.Containers!Modules. _
Documents![Utility Functions]
Set prp = doc.CreateProperty _
("KeepLocal", dbText, "T")
doc.Properties.Append prp
Set doc = dbs.Containers!Reports. _
Documents![Sales by Category]
Set prp = doc.CreateProperty _
("KeepLocal", dbText, "T")
doc.Properties.Append prp
cmdKeepLocal_Click_Done:
Exit Sub
cmdKeepLocal_Click_Error:
Select Case Err
'If property appended already, set to T
Case 3367
doc.Properties("KeepLocal").Value = "T"
Case Else
MsgBox Err & _
"is an unexpected error number."
End Select
Resume Next
End Sub
Since the KeepLocal property must be appended to a properties collection before you can use it, the procedure checks to determine if the property exists for an object. There are three steps to keeping an object local. First, set a reference for the object as a doc container. Second, create the KeepLocal property for the container and set it to "T". Third, append the KeepLocal setting to the doc container's properties collection.
After making a design master, you can use the frmNewReplica form to add one or more ordinary replicas to the replica set. This form has two text boxes for the source replica and two more for the target replica. Type the path name and filename for the source in the first two boxes. Enter into the second two text boxes the target path name and filename. Click the button labeled Make Replica to launch the process.
The private procedure invoked by clicking the button on frmNewReplica concatenates the path names and filenames to form full descriptions for the source and target replicas and calls the NewReplica procedure:
Public Sub NewReplica(strReplicableDB As String, _
strNewReplica As String)
Dim db As DATABASE
Set db = DBEngine(0).OpenDatabase( _
strReplicableDB)
db.MakeReplica strNewReplica, _
"Description goes here " & strReplicableDB
db.Close
End Sub
The NewReplica procedure works in two steps. First, it opens the source replica. Second, it applies the MakeReplica method to create the new replica. This simple procedure highlights how to use the MakeReplica method, but it includes no error trapping. One obvious error you may wish to check for would be an attempt to replicate a file that doesn't exist.
A key reason for creating two or more replicas is to perform synchronizations. The Synchronize method accomplishes this task. The method propagates changes between a source replica and its target. An optional constant after the target name designates whether the transfer is unidirectional or bidirectional.
The event procedure attached to the cmdSyncDBs button on frmSyncDBs form (see Figure 3) calls the SyncDb subroutine, which is shown here:
Public Sub SyncDb(strDBName, strSyncTargetDB)
Dim db As Database
Set db = DBEngine(0).OpenDatabase(strDBName)
db.Synchronize strSyncTargetDB
db.Close
End Sub
This subroutine works by using the Synchronize method of the database object. Before returning control, it closes the source replica. In order to view the synchronization changes on the target database, you need to close and open the database.
Figure 3. You can use frmSyncDBs to synchronize two replicas.
Access 95 replication isn't well suited to two data sharing scenarios. First, if two or more replicas will routinely change the same records, Access standard conflict resolution techniques may prove cumbersome. Of course, this is probably a problem with replication in general: if you expect a large number of conflicts, you may find you're spending too much time or program resources resolving conflicts. Second, if updates must be propagated instantly, then you're better off using a classic file/server or client/server model. Access' multimaster model communicates updates only when replicas synchronize with each other.
I hope this article motivates and equips you to use replication. By accommodating load sharing and mobile worker needs, replication significantly increases the number of users who can benefit from Access applications. It does this without some of the traditional problems associated with multiuser applications (for example, multiuser locking). While the Access documentation, the replication white paper distributed with the Access Developer's Toolkit (and included on this month's Companion Disk), and this article are useful resources, the best way to discover the wonders of replication is experiment with replication yourself.
Rick Dobson, Ph.D., is president of CAB Inc., a database application development firm. Recent clients include Mead Corporation and Kroger Supermarkets. He has written articles for Byte, and PC. Fax 502-426-3743; Internet Rick_Dobson@msn.com.
To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/access/.
Note: This is not a
Microsoft Corporation website.
Microsoft is not responsible for its content.
This article is reproduced from the April 1996 issue of SmartAccess. Copyright 1996, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SmartAccess is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call (800)788-1900 or (206)251-1900.