Implementing Database Replication with JRO

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Microsoft Corporation

January 1999

The Microsoft® Jet and Replication Objects (JRO) model contains objects, properties, and methods for creating, modifying, and synchronizing replicas. It is designed specifically for use with the Microsoft Jet OLE DB Provider. Unlike ActiveX® Data Objects (ADO) and ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX), JRO can't be used with data sources other than Microsoft Jet databases (.mdb files).

The primary object in the JRO model is the Replica object. The Replica object is used to create new replicas, retrieve and modify properties of an existing replica, and synchronize changes with other replicas. This differs from Data Access Objects (DAO), where the Database object is used for these tasks.

**Note   **The code samples in this paper are available in the Replication module of the MultiuserDatabase.mdb sample file in the Samples\CH16 folder on the companion CD-ROM to the Microsoft Office 2000/Visual Basic® Programmer's Guide (Microsoft Press®, 1999).

Making a Database Replicable

The first step in enabling replication is to create a Design Master. The Design Master is where you make structural changes to the database that you want to disperse to the replica set; changes to data are made in the replicas contained in the replica set. Making a database replicable makes the database a Design Master.

The JRO model simplifies the code for making a database replicable. To make a database replicable by using DAO, the database must be opened, the Replicable property must be created and appended to the Properties collection of the database, and then the Replicable property must be set to "T". With JRO, a database can be made replicable with a single method, MakeReplicable, as shown in the following example:

Sub MakeRecordLevelDesignMaster(strDBPath As String)
   Dim repMaster As New JRO.Replica
   
   ' Make the database replicable.
   ' If successful, this will create a connection to the
   ' database.
   repMaster.MakeReplicable strDBPath, False
   
   Set repMaster = Nothing
End Function

The MakeReplicable method in JRO has an optional second argument named ColumnTracking, which is set to False in the example above. The ColumnTracking argument indicates whether changes are tracked at the column (field) level or at the record level. DAO does not expose the ability to track changes at the column level. Therefore, this argument must be set to False if you want the same behavior as DAO. For more information about column-level change tracking, see "Column-Level Conflict Resolution" later in this document.

**Important   **As with DAO, the process of making a database replicable by using JRO can't be reversed. It is strongly recommended that you make a backup of your database before performing this operation.

Creating a Replica

The code for creating a replica with JRO is similar to the code from DAO. In ADO, setting the ActiveConnection property of a Replica object opens the Design Master or another existing replica. Once the Design Master or an existing replica is open, you can create the new replica by calling the CreateReplica method. The following code shows how to create a full, read/write replica of an existing Design Master or replica by using JRO:

Function MakeNewReplica(strReplicableDBPath As String, _
                        strDescription As String) As Integer
   Dim repReplicableDB          As New JRO.Replica
   Dim strNewReplicaDBPath      As String

   strNewReplicaDBPath = GetReplicaName(strReplicableDBPath)
   repReplicableDB.ActiveConnection = strReplicableDBPath
   repReplicableDB.CreateReplica strNewReplicaDBPath, strDescription, _
      jrRepTypeFull, jrRepVisibilityGlobal
   Set repReplicableDB = Nothing
End Function

For example, to create a new replica of the NorthwindDM.mdb Design Master, you would use the following line of code:

MakeNewReplica "c:\NorthwindDM.mdb", "c:\NwindReplica.mdb"

The DAO MakeReplica method has an optional argument named Options. This argument allows you to indicate the type of replica to create: full or partial, read-only or read/write. In JRO, there are two optional arguments named Type and Updatability. The Type argument allows the user to indicate whether the replica should be full or partial. The Updatability argument allows the user to indicate whether the replica is read-only or fully updatable.

The following table shows how the optional argument and constants for the DAO MakeReplica method map to those for the JRO CreateReplica method.

DAO JRO
Options:=dbRepMakePartial Type:=jrRepTypePartial
Options:=dbRepMakeReadOnly Updatability:=jrRepUpdReadOnly

The JRO CreateReplica method has two additional optional arguments named Visibility and Priority. These arguments are omitted in the JRO code example above, indicating that the default values should be used. The Visibility and Priority arguments are new in JRO and provide additional control over how synchronizations with the replica will be performed. The default value for each of these arguments maps to the DAO behavior. For more information about replica visibility and priority, see "Replica Visibility" and "Replica Priority" later in this document.

Creating a Partial Replica

It is often desirable to create replicas that contain a subset of the data contained in another replica. For example, a business may want to store its entire sales database at its headquarters but distribute only regional data to its offices across the country. To do this, the business can create a separate replica for each regional office that contains only the data relating to that region. The database at the headquarters would be a full replica, with which each partial replica would be synchronized.

There are two ways to filter the data in a partial replica. The first method is by using an expression, similar to an SQL WHERE clause (without the word "WHERE"), to specify which records to filter. The second method is to filter data by using a relationship. Relationship filters allow you to enforce the relationship when you are replicating data. They are generally used in conjunction with an expression-based filter.

The process for creating a partial replica is the same in JRO as it is in DAO. With both models, the process is as follows:

  1. Create the partial replica.
  2. Create the filter or filters.
  3. Populate the partial replica by using the filters.

The primary difference between the two models is in creating the filters. DAO exposes properties of the Table and Relation objects for creating filters. JRO has a single Filters collection and uses the Append method to create new filters.

The following code shows how to create a new partial replica and how to populate the partial replica with data limited by both an expression-based filter and a relationship-based filter:

Sub CreatePartial(strReplicableDBPath As String, _
                  StrDescription As String)
   ' This procedure creates a new, partial replica with global visibility.
   ' It assumes you are using the Northwind sample database, which contains
   ' the Customers table and the CustomersOrders relationship.

   Dim repFull               As New JRO.Replica
   Dim repPartial            As New JRO.Replica
   Dim strPartialDBPath      As String

   strPartialDBPath = GetReplicaName(strReplicableDBPath, strDescription)

   ' Create partial replica.
   repFull.ActiveConnection = strReplicableDBPath
   repFull.CreateReplica  strPartialDBPath, strDescription, _ 
      jrRepTypePartial, jrRepVisibilityGlobal

   Set repFull = Nothing
   
   ' Open the partial replica in exclusive mode and create an 
   ' expression-based filter in the partial replica.
   repPartial.ActiveConnection = "Data Source=" & strPartialDBPath _
      & ";Mode=Share Exclusive"
   repPartial.Filters.Append "Customers", jrFilterTypeTable, _
      "Region = 'CA'"

   ' Create a filter based on a relationship in the partial replica.
   repPartial.Filters.Append "Orders", jrFilterTypeRelationship, _
      "CustomersOrders"

   ' Repopulate the partial replica based on the filters.
   repPartial.PopulatePartial strReplicableDBPath

   Set repPartial = Nothing
End Sub

The following example demonstrates how to list all of the filters for a partial replica by looping through the Filters collection with a For Each…Next loop:

Sub ListFilters(strPartialDBPath As String)
   Dim repPartial         As New JRO.Replica
   Dim flt               As JRO.Filter
   Dim strFilterType      As String

   repPartial.ActiveConnection = strPartialDBPath

   For Each flt In repPartial.Filters
      If flt.FilterType = jrFilterTypeTable Then
         strFilterType = "Table Filter"
      Else
         strFilterType = "Relationship Filter"
      End If
      Debug.Print flt.TableName & " : " & strFilterType & " : " & _
         flt.FilterCriteria
   Next

   Set repPartial = Nothing
End Sub

Making Objects Local or Replicable

By default, when a database is made into a Design Master, all objects in that database will be replicated. If you don't want a table or query to be replicated (that is, you want it to remain local), you must indicate that the object should remain local before you make replicas from the Design Master.

In contrast, when you create a new table or query in a Design Master, the object is considered local and is stored only in the Design Master. If you want to distribute the object to members of the replica set, you must make the object replicable.

With JRO, the GetObjectReplicability and SetObjectReplicability methods can be used, both before and after the database is made into a Design Master, to determine or set whether the object is or will be replicated. These methods take the name of the object you want to get or set replicability for, the type of the object, and a Boolean value that indicates whether it should be kept local or made replicable.

**Note   **The SetObjectReplicability method can be used only to specify the replicability of tables and queries. The SetObjectReplicability method is ignored when it is used on Access-specific objects (forms, reports, data access pages, macros, and modules). To specify the replicability of Access-specific objects, you must use either the DAO KeepLocal property or the DAO ReplicableBool property.

This example shows how to indicate that a table or query should be kept local when replicas are made from the Design Master.

Sub KeepObjectLocal(strDBPath As String, _
                    strObjectName As String, _
                    strObjectType As String)
   Dim repMaster     As New JRO.Replica

   repMaster.ActiveConnection = strDBPath

   repMaster.SetObjectReplicability strObjectName, strObjectType, False

   Set repMaster = Nothing
End Sub

For example, to keep the Quarterly Orders query in the NorthwindDM.mdb Design Master from being replicated, you would use the following line of code:

KeepObjectLocal "c:\NorthwindDM.mdb", "Quarterly Orders", "Tables"

**Important   **Even though the SetObjectReplicability property provides an ObjectType argument to specify the type of object you are working with, the argument only accepts a value of "Tables" for both queries and tables.

This next example shows how to make a new object in the Design Master replicable:

Sub MakeObjectReplicable(strDBPath As String, _
                         strObjectName As String, _
                         strObjectType As String)
   Dim repMaster      As New JRO.Replica

   repMaster.ActiveConnection = strDBPath

   repMaster.SetObjectReplicability strObjectName, strObjectType, True

   Set repMaster = Nothing
End Sub

For example, to make the MyTable table replicable in the NwindReplica.mdb replica, you would use the following line of code:

MakeObjectReplicable "c:\NorthwindDM.mdb", "MyTable", "Tables"

Synchronizing Data

Synchronizing two replicas involves exchanging data and design changes. Synchronization can be bidirectional (that is, changes in each replica are propagated to the other), or it can occur in a single direction.

Microsoft Jet also supports two styles of synchronization:

  • Direct synchronization is when the synchronization process has a direct connection to both replicas and is able to open both replicas simultaneously. Updates are immediately read and written to both replicas.
  • Indirect synchronization is when there is no direct connection to the remote replica. Instead the local replica collects its updates into a "message" file, which is written to a predetermined location, referred to as a dropbox folder. At some later time, the remote replica looks at its dropbox folder, reads the message file and applies the updates. Indirect synchronization can either send the message file over the file system or the Internet or an intranet.

Direct synchronization is the default method used by Microsoft Jet replication and is ideal when the replicas are on a local area network (LAN). Direct synchronization is usually a poor choice when replicas are physically dispersed and synchronization is over a wide area network (WAN) or dial-up connection.

Indirect synchronization is almost always the better choice for synchronizing dispersed replicas over a WAN or dial-up connection. With indirect synchronization, you never open the remote replica over a potentially unreliable communications link. Instead synchronization occurs in two phases, with each phase being controlled by a process running locally to each replica. Since you are never opening a replica remotely, indirect synchronization is both fast and robust.

The following code demonstrates how to synchronize changes between two replicas. The first example shows how to do a direct, two-way synchronization.

Sub TwoWayDirectSync(strReplica1 As String, _
                     strReplica2 As String)
   Dim repReplica  As New JRO.Replica

   repReplica.ActiveConnection = strReplica1

   ' Sends changes made in each replica to the other.
   repReplica.Synchronize strReplica2, jrSyncTypeImpExp, _
      jrSyncModeDirect

   Set repReplica= Nothing
End Sub

For example, to synchronize the Design Master of the Northwind database with a member of its replica set, you would use the following line of code:

TwoWayDirectSync "c:\NorthwindDM.mdb", "c:\NwindReplica2.mdb"

The following example shows how to do a two-way synchronization over the Internet.

**Note   **To configure an Internet or intranet server so that you can synchronize replicas, you must install Replication Manager, which is available with Microsoft Office 2000 Developer. For more information about installing and running Replication Manager, see Microsoft Office 2000 Developer Help. For the latest information about configuring and running Replication Manager, search the Office Developer product section of the Microsoft Support Online Web site (http://support.microsoft.com/support/default.asp) for the keyword phrase "Replication Manager."

Sub InternetSync(strLocalReplicaPath As String, _
                 strServerReplicaURL As String)
   Dim repLocalReplica  As New JRO.Replica

   repLocalReplica.ActiveConnection = strLocalReplicaPath

   ' Synchronize the local replica with the replica on
   ' the Internet server.
   repLocalReplica.Synchronize strServerReplicaURL, jrSyncTypeImpExp, _
      jrSyncModeInternet

   Set repLocalReplica = Nothing
End Sub

For example, to synchronize the OrderEntry.mdb replica on your local machine with the Orders.mdb replica on the Internet server, you would use the following line of code:

InternetSync "c:\Data\OrderEntry.mdb", "www.mycompany.myserver.com/files/Orders.mdb"

The JRO and DAO code for performing a two-way, direct synchronization between two replicas is similar. However, note that the JRO Synchronize method has an additional SyncMode argument that specifies whether the synchronization is direct (jrSyncModeDirect) or indirect (jrSyncModeIndirect). For functionality equivalent to DAO, you must specify jrSyncModeDirect when calling the Synchronize method. If you omit the SyncMode argument, the synchronization will be performed indirectly. The ability to programmatically perform indirect synchronizations is a new feature in JRO, designed to increase performance when you are synchronizing over a WAN. For more information about performing indirect synchronizations, see "Indirect Synchronization" later in this document.

The following table shows the mapping between the constants taken by the DAO Exchange argument of the Synchronize method and the constants taken by the JRO SyncType and SyncMode arguments.

DAO JRO
Exchange:=dbRepExportChanges SyncType:=jrSyncTypeExport
Exchange:=dbRepImportChanges SyncType:=jrSyncTypeImport
Exchange:=dbRepImExpChanges SyncType:=jrSyncTypeImpExp
Exchange:=dbRepSyncInternet SyncMode:=jrSyncModeInternet

Listing Synchronization-Conflict Tables

If two users who are modifying two separate replicas each make a change to the same record in the database, a conflict may occur. If changes are being tracked at the record level, a conflict will occur if two users make a change to the same record. If changes are being tracked at the column (field) level, a conflict will occur if two users make a change to the same field in the same record. When a conflict occurs, the changes made by one user will fail to be applied to the other replica. Information regarding the conflict will be replicated to both replicas.

Information about the conflict is contained in a conflict table in each replica. Conflict tables contain the information that would have been placed in the table if the change had been successful. You can examine these conflict tables and work through them record-by-record, resolving the conflicts as appropriate.

The following code demonstrates how to determine whether conflicts occurred during synchronization and, if conflicts did occur, how to retrieve the names of the conflict tables that were created.

Sub RetrieveConflictTables(strReplicableDBPath As String)
   Dim repMaster      As New JRO.Replica
   Dim rstConflicts   As ADODB.Recordset

   repMaster.ActiveConnection = strReplicableDBPath

   Set rstConflicts = repMaster.ConflictTables

   If rstConflicts.BOF and rstConflicts.EOF Then
      ' There are no conflict tables, so no conflicts occurred.
      Debug.Print "No conflicts."
   Else    
      While Not rstConflicts.EOF
         Debug.Print rstConflicts.Fields(0) & " had a conflict."
         rstConflicts.MoveNext
      Wend
   End If
End Sub

With JRO, the ConflictTables property of the Replica object is used to determine which tables had conflicts. This property returns an ADO Recordset object that contains one record for each table that contains conflicts. With the ConflictTables property, it is easy to determine whether conflicts occurred. If the Recordset object is empty (the BOF and EOF properties of the Recordset object are both True), no errors occurred. With DAO, you must check the ConflictTable property for each table in the TableDefs collection to determine whether conflicts occurred and what the name of the related conflict table is.

New Features in JRO

Replication in Microsoft Jet 4.0 databases (.mdb files) provides additional features that aren't exposed to DAO. The following sections describe how to use these features from JRO.

Replica Visibility

JRO introduces a new property of a replica that is used to indicate the visibility of a replica to other replicas. The visibility of a replica determines which other replicas that replica can synchronize with. A replica's visibility may be global, local, or anonymous. The replica's visibility is set when the replica is first created. Once the replica is created, its visibility can't be changed.

  • Global replica—A global replica can synchronize with any other global replica in the set. Changes made to a global replica are fully tracked. A global replica can also exchange changes with any local or anonymous replicas made from it. This is the only kind of replica that you can create in previous versions of Access and Microsoft Jet. From a global replica, you can create replicas that are global, local, or anonymous. Replicas created from a global replica are global by default.
  • Local replica—A local replica can synchronize only with its parent (the global replica it was created from) and won't be permitted to synchronize with other replicas in the replica set. Local replicas always have a priority of 0; therefore, if any of their changes conflict with their parent replica, the changes made in the local replica will automatically lose in any conflict-resolution process. If local replicas convey nonconflicting changes to their parent replica, the parent assumes authorship of the changes. Other replicas will not be aware of local replicas. Only the parent replica can schedule a synchronization with a local replica. All replicas created from a local replica will also be local and will inherit the same parent replica.
  • Anonymous replica—An anonymous replica can synchronize only with its parent (a global replica). Anonymous replicas have a priority of 0 and follow the same conflict-resolution rules as local replicas. No replica (including the parent replica) is aware of an anonymous replica. The parent replica can't schedule an exchange to an anonymous replica. However, because system-tracking information is not maintained and replica size is reduced, anonymous replicas are recommended for use on the Internet. All replicas created from an anonymous replica will also be anonymous and will inherit the same parent replica.

The following code demonstrates how to create a new anonymous replica:

Sub MakeAnonReplica(strReplicableDB As String, _
                    strDescription As String)
   ' This procedure creates a full replica with anonymous visibility.
   Dim repMaster               As New JRO.Replica
   Dim strNewReplicaDBPath      As String
   
   strNewReplicaDBPath = GetReplicaName(strReplicableDB, strDescription)
   repMaster.ActiveConnection = strReplicableDB
   repMaster.CreateReplica strNewReplicaDBPath, strDescription, _
      jrRepTypeFull, jrRepVisibilityAnon

   Set repMaster = Nothing
End Sub

Replica Priority

JRO introduces a new property of a replica that is used to indicate the relative priority of a replica during synchronization. If conflicts are encountered during synchronization, the replica with the highest priority wins.

To specify a replica's priority, you set the Priority argument when you are creating the replica by using the CreateReplica method. The valid range of values for the Priority argument is from 0 to 100. After a replica is created, its priority value is read-only. You can read a replica's priority by using the Priority property.

The algorithm for resolving conflicts based on a replica's priority is as follows:

  • The replica with the highest priority wins.
  • If priorities are equal, the replica with the lowest ReplicaId property value wins. The ReplicaID property is a globally unique identifier (GUID) value that uniquely identifies a database replica. The ReplicaID property's value is read-only and is automatically generated when the replica is created.

In previous versions of Microsoft Jet, the replica with the most changes in the record prevails; in the event that both replicas have the same number of changes, the second rule listed above applies. To ensure compatibility when older databases are converted, the priority for all converted replicas is set equal to 90.

For global replicas, the default priority is 90 percent of the parent replica's priority. In addition, the valid values for a global replica may be further restricted. If the user is the database administrator, the entire range is valid. Otherwise, the maximum value for priority is 90 percent of the parent replica's priority. For local and anonymous replicas, the value is always 0 and can't be changed.

The following code demonstrates how to set the priority when creating a new replica:

Sub MakePriorityReplica(strReplicableDB As String, _
                        strDescription As String, _
                        lngPriority As Long)
   ' This procedure creates a new replica with global visibility and sets
   ' its priority to the specified value.
   
   Dim repMaster               As New JRO.Replica
   Dim repNewReplica            As New JRO.Replica
   Dim strNewReplicaDBPath      As String
   
   strNewReplicaDBPath = GetReplicaName(strReplicableDB, strDescription & _
      lngPriority)
      
   repMaster.ActiveConnection = strReplicableDB

   repMaster.CreateReplica strNewReplicaDBPath, strDescription & lngPriority, _
      jrRepTypeFull, jrRepVisibilityGlobal, lngPriority
      
   
   Set repMaster = Nothing
   
   repNewReplica.ActiveConnection = strNewReplicaDBPath
   
   Debug.Print "New Replica's Priority = " & repNewReplica.Priority

   Set repNewReplica = Nothing
End Sub

Indirect Synchronization

With a direct synchronization, your machine is tied up until the synchronization is complete. On fast LANs, this may not be an issue. However, synchronization over a slow WAN may take a very long time. Indirect synchronization was designed for this scenario. For an indirect synchronization, the local replica leaves the changes in a message file in a dropbox folder on a server so that the changes can later be picked up and applied by the remote replica.

The following code demonstrates how to perform a two-way indirect synchronization:

Sub TwoWayIndirectSync(strReplica1 As String, _
                       strReplica2 As String)
   Dim repReplica  As New JRO.Replica

   repReplica.ActiveConnection = strReplica1

   ' Sends changes made in each replica to the other.
   repReplica.Synchronize strReplica2, jrSyncTypeImpExp, _
      jrSyncModeIndirect

   Set repReplica = Nothing
End Sub

For example, to perform a two-way indirect synchronization between the Northwind.mdb local replica and the NwindReplica.mdb remote replica, you would use the following line of code:

TwoWayIndirectSync "c:\Northwind.mdb", " \\MyRemoteServer\MyShare\NwindReplica.mdb"

Synchronizing Changes with a Microsoft SQL Server Database

JRO supports synchronizing changes between a Microsoft SQL Server 7.0 publication (replica) and a Microsoft Access database (.mdb file). You can create a SQL Server publication that contains an Access database only from SQL Server 7.0. SQL Server 7.0 documentation refers to this as replicating to heterogeneous data sources and as publishing to a heterogeneous subscriber. For information about creating a SQL Server 7.0 publication that contains an Access database, see the documentation supplied with SQL Server.

To specify the SQL Server publication to synchronize with, you must use this syntax for the Target argument of the Synchronize method Server.Database.Publication where Server is the name of the SQL Server machine that contains the published database, Database is the name of the published database, and Publication is the name of the publication itself. For example, if the server is named PubData, the database is named Pubs, and the publication is named Pubs1, the Target argument would be specified as:

PubData.Pubs.Pubs1

Additionally, the SyncMode argument of the Synchronize method must be set to jrSyncModeDirect because only direct synchronization is supported in this situation.

The following code demonstrates how to perform a Microsoft Jet-to-SQL Server synchronization:

Sub JetSQLSync(strJetDatabase As String, _
               strSQLServer As String)
   Dim repReplica  As New JRO.Replica

   repReplica.ActiveConnection = strJetDatabase

   ' Sends changes made in each replica to the other.
   repReplica.Synchronize strSQLServer, jrSyncTypeImpExp, _
      jrSyncModeDirect

   Set repReplica= Nothing
End Sub

For example, to synchronize the Microsoft Jet Pubs.mdb database with the Pubs1 SQL Server publication, you could use a line of code like this:

JetSQLSync "c:\Pubs.mdb", "PubData.Pubs.Pubs1"

Column-Level Conflict Resolution

Column-level conflict resolution allows the synchronization process to merge two records and only report a conflict if simultaneous changes have been made to the same column (field). In order for the synchronization process to perform column-level conflict resolution, column-level change tracking must be enabled. If you frequently have overlapping updates in the same record, enabling column-level conflict tracking may improve performance.

Column-level conflict tracking is set when a database is made replicable; it can't be changed once the process of making the database replicable is complete. Column-level conflict resolution is turned on by default; you can also turn it on explicitly by setting the optional ColumnTracking argument of the MakeReplicable method to True. To turn off column-level conflict tracking, you must set the ColumnTracking argument of the MakeReplicable method to False, as shown in "Making a Database Replicable," earlier in this document.

The following code demonstrates how to explicitly turn on column-level tracking when making a database replicable:

Sub MakeColumnLevelDesignMaster(strDBPath As String)
   Dim repMaster As New JRO.Replica

   repMaster.MakeReplicable strDBPath, True

   Set repMaster = Nothing
End Function

--------------------------------------------

© 1999 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, this paper should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Microsoft, ActiveX, Microsoft Press, and Visual Basic are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.