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).
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.
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.
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:
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
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 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 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 |
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.
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.
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.
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
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:
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
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"
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 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.