Replication enables users at different locations to easily share the changes they are making to a database. Copies of a database, called replicas, can be made and distributed to users at different locations. Users at each location can work on their local copy and then share, or synchronize, their changes with users at other locations.
Note Use JRO, not ADO, to implement replication in your application.
The first step in enabling replication is to create a design master. A design master is the only replica in the replica set that can make both schema and data changes—all other replicas can only make data changes to replicated objects. Making a database replicable makes the database a design master.
The following listings demonstrate how to make an existing database replicable.
DAO
Sub DAOMakeDesignMaster()
Dim dbsNorthwind As DAO.Database
Dim prpNew As DAO.Property
' Open database for exclusive access.
Set dbsNorthwind = DBEngine.OpenDatabase(".\NorthWind.mdb", True)
With dbsNorthwind
' If Replicable property doesn't exist, create it.
' Turn on error handling in case property exists.
On Error Resume Next
' Make the Northwind database replicable
Set prpNew = .CreateProperty("Replicable", dbText, "T")
.Properties.Append prpNew
.Close
End With
End Sub
JRO
Sub JROMakeDesignMaster()
Dim repMaster As New JRO.Replica
' Make the Northwind database replicable.
' If successful, this will create a connection to the
' database.
repMaster.MakeReplicable ".\NorthWind.mdb", False
Set repMaster = Nothing
End Sub
The JRO model simplifies the code for making a database replicable. To make a database replicable using DAO, the database must be opened, the Replicable property must be created with the initial value set to "T," and finally the property must be appended to the Properties collection of the database. With JRO a database can be made replicable with a single method, MakeReplicable.
The MakeReplicable method in JRO has an optional second parameter named ColumnTracking set to False in the previous example. It indicates whether changes are tracked at the column level or row level. DAO did not expose the ability to track changes at the column level. Therefore, this parameter must be set to False if you want the same behavior as DAO. See the section "New Features in ADO, ADOX, and JRO" for more information on column-level tracking.
As with DAO, the process of making a database replicable using JRO cannot be reversed. It is recommended that you make a backup of your database before performing this operation.
By default, when a database is made replicable all objects in that database will be replicated. If you do not want an object replicated you must indicate that the object should not be replicated (that is, it should remain local) before you make the database replicable.
In contrast, when you create a new table, query, form, report, macro, or module at a replica, the object is considered local and is stored only at that replica. If you want users at other replicas to be able to use the object, you must make it replicable.
This following listings demonstrate how to indicate that an object should be kept local when the database is made replicable.
DAO
Sub DAOKeepObjectLocal()
Dim dbsNorthwind As DAO.Database
Dim docTemp As DAO.Document
Dim prpTemp As DAO.Property
Set dbsNorthwind = DBEngine.OpenDatabase(".\NorthWind.mdb")
Set docTemp = _
dbsNorthwind.Containers("Tables").Documents("Contacts")
Set prpTemp = docTemp.CreateProperty("KeepLocal", dbText, "T")
docTemp.Properties.Append prpTemp
dbsNorthwind.Close
End Sub
JRO
Sub JROKeepObjectLocal()
Dim repMaster As New JRO.Replica
repMaster.ActiveConnection = ".\NorthWind.mdb"
repMaster.SetObjectReplicability "Contacts", "Tables", False
Set repMaster = Nothing
End Sub
This next example shows how to make a new object in a replica replicable.
DAO
Sub DAOMakeObjectReplicable(strTable As String)
Dim dbsNorthwind As DAO.Database
Dim tdfTemp As DAO.TableDef
Set dbsNorthwind = DBEngine.OpenDatabase(".\NorthWind.mdb")
Set tdfTemp = dbsNorthwind.TableDefs(strTable)
On Error GoTo ErrHandler
tdfTemp.Properties("Replicable") = "T"
On Error GoTo 0
dbsNorthwind.Close
Exit Sub
ErrHandler:
Dim prpNew As DAO.Property
If Err.Number = 3270 Then
Set prpNew = tdfTemp.CreateProperty("Replicable", dbText, "T")
tdfTemp.Properties.Append prpNew
Else
MsgBox "Error " & Err & ": " & Error
End If
End Sub
JRO
Sub JROMakeObjectReplicable(strTable As String)
Dim repMaster As New JRO.Replica
repMaster.ActiveConnection = ".\NorthWind.mdb"
repMaster.SetObjectReplicability strTable, "Tables", True
Set repMaster = Nothing
End Sub
With DAO two properties, Replicable and KeepLocal, determine whether an object is or will be replicated. Use the KeepLocal property prior to making the database replicable to indicate that the object should not be made replicable when the database is made replicable. Use the Replicable property after the database is made replicable to indicate whether the object should be replicated. DAO requires you to create the properties using the CreateProperty method of the object's Properties collection before you can set them.
With JRO the GetObjectReplicability and SetObjectReplicability methods are used, both before and after the database is made replicable, to determine or set whether the object is or will be replicated. The method takes the name of the object you wish 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.
The following pseudocode is the algorithm for mapping the DAO KeepLocal and Replicable properties to the ADO ObjectReplicability:
If DAO.Database.Replicable = 'T'
If DAO.Object.Replicable = 'T'
JRO.ObjectReplicability = True
Else
JRO.ObjectReplicability = False
Else
If DAO.Object.KeepLocal = 'T'
JRO.ObjectReplicability = False
Else
JRO.ObjectReplicability = True
The following listings demonstrate how to create a full, read/write replica of an existing replica using DAO and then using JRO.
DAO
Function DAOMakeAdditionalReplica(strReplicableDB As String, _
strNewReplica As String) As Integer
Dim dbsTemp As DAO.Database
Set dbsTemp = DBEngine.OpenDatabase(strReplicableDB)
dbsTemp.MakeReplica strNewReplica, "Replica of " & strReplicableDB
dbsTemp.Close
End Function
JRO
Function JROMakeAdditionalReplica(strReplicableDB As String, _
strNewReplica As String) As Integer
Dim repMaster As New JRO.Replica
repMaster.ActiveConnection = strReplicableDB
repMaster.CreateReplica strNewReplica, "Replica of " & _
strReplicableDB
Set repMaster = Nothing
End Function
The code for creating a replica with JRO is similar to the DAO code. Both examples begin with opening or connecting to the design master. In DAO the design master is opened with the DBEngine object's OpenDatabase method. In ADO setting the Replica object's ActiveConnection property opens the design master. Once it is open, the new replica is created by calling a method to create the replica. The JRO equivalent to the DAO MakeReplica method is CreateReplica.
The DAO MakeReplica method has an optional parameter named Options. This parameter allows you to indicate the type of replica to create: full or partial, read-only or read/write.
In JRO there are two optional parameters named Type and Updatability. The Type parameter allows the user to indicate whether the replica should be full or partial. The Updatability parameter allows the user to indicate whether the replica is read-only or fully updatable.
The following table shows how the optional parameters and constants for the DAO MakeReplica method map to those for the JRO CreateReplica method.
| DAO parameter | DAO constant | JRO parameter | JRO constant |
| Options | dbRepMakePartial | Type | jrRepTypePartial |
| Options | dbRepMakeReadOnly | Updatability | jrRepUpdReadOnly |
The JRO CreateReplica method has two additional, optional parameters named Visibility and Priority. These parameters are omitted in the preceding JRO code example, indicating that the default value should be used. Visibility and Priority are new in JRO and provide additional control over how synchronizations with the replica will be performed. The default value for each of these parameters maps to the DAO behavior. See the section "New Features in ADO, ADOX, and JRO" for more information about replica visibility and priority.
Sometimes, it is necessary to create replicas that contain a subset of the data contained in another replica. For example, a business might store its entire sales database at the headquarters office but replicate only regional data to its regional offices across the country. You can create a separate replica for each regional office that contains only the data relating to that region. The database at the headquarters office 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 an expression, similar to an SQL WHERE clause (without the word WHERE). With an expression-based filter, the records in the table are limited to those that satisfy the expression. The second method to filter data is with a relationship filter. Relationship filters allow you to enforce the relationship when replicating data. It is generally used in conjunction with an expression-based filter.
The following listings demonstrate how to create a new partial replica and then populate the data in the partial replica limited by both an expression based filter and a relationship based filter.
DAO
Sub DAOCreatePartial()
Dim dbsFull As DAO.Database
Dim dbsPartial As DAO.Database
Dim tdfCustomers As DAO.TableDef
Dim relCustOrders As DAO.Relation
' Create partial replica.
Set dbsFull = DBEngine.OpenDatabase(".\NorthWind.mdb")
dbsFull.MakeReplica ".\FY96.mdb", "Partial Sales Replica", _
dbRepMakePartial
dbsFull.Close
' Create an expression based filter in the partial replica.
Set dbsPartial = DBEngine.OpenDatabase(".\FY96.mdb", True)
Set tdfCustomers = dbsPartial.TableDefs("Customers")
tdfCustomers.ReplicaFilter = "Region = 'CA'"
' Create a filter based on a relationship in the partial replica.
Set relCustOrders = dbsPartial.Relations("CustomersOrders")
relCustOrders.PartialReplica = True
' Repopulate the partial replica based on the filters.
dbsPartial.PopulatePartial ".\NorthWind.mdb"
dbsPartial.Close
End Sub
JRO
Sub JROCreatePartial()
Dim repFull As New JRO.Replica
Dim repPartial As New JRO.Replica
' Create partial replica.
repFull.ActiveConnection = ".\NorthWind.mdb"
repFull.CreateReplica ".\FY96.mdb", "Partial Sales Replica", _
jrRepTypePartial
Set repFull = Nothing
' Create an expression based filter in the partial replica.
' The PopulatePartial method requires an exclusive connection
repPartial.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\FY96.mdb;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 ".\NorthWind.mdb"
Set repPartial = Nothing
End Sub
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: Create the partial replica, create the filter(s), and populate the partial replica 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 Filters collection. Use the Filters collection Append method to create new filters.
The following listings demonstrate how to list all of the filters for a partial replica.
DAO
Sub DAOListFilters()
Dim dbPartial As DAO.Database
Dim tbl As DAO.TableDef
Dim rel As DAO.Relation
Set dbPartial = DBEngine.OpenDatabase(".\FY96.mdb")
For Each tbl In dbPartial.TableDefs
If tbl.ReplicaFilter <> "" Then
Debug.Print tbl.Name & " : Table Filter : " & _
tbl.ReplicaFilter
End If
Next
For Each rel In dbPartial.Relations
Debug.Print rel.Name & " : Relationship Filter";
If rel.PartialReplica Then
Debug.Print " : Partial";
End If
Debug.Print
Next
dbPartial.Close
End Sub
JRO
Sub JROListFilters()
Dim repPartial As New JRO.Replica
Dim flt As JRO.Filter
Dim strFilterType As String
repPartial.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\FY96.mdb"
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
Synchronizing two replicas involves exchanging data and design changes. Synchronization can be bi-directional (that is, changes in each replica are propagated to the other), or can occur in a single direction.
The following listing demonstrates how to synchronize changes between two replicas. The first example shows how to do a direct, two-way synchronization.
DAO
Sub DAOTwoWayDirectSync()
Dim dbsNorthwind As DAO.Database
Set dbsNorthwind = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Sends changes made in each replica to the other.
dbsNorthwind.Synchronize ".\FY96.mdb", dbRepImpExpChanges
dbsNorthwind.Close
End Sub
JRO
Sub JROTwoWayDirectSync()
Dim repMaster As New JRO.Replica
repMaster.ActiveConnection = ".\NorthWind.mdb"
' Sends changes made in each replica to the other.
repMaster.Synchronize ".\FY96.mdb", jrSyncTypeImpExp, _
jrSyncModeDirect
Set repMaster = Nothing
End Sub
The following example shows how to do a two-way synchronization over the Internet.
DAO
Sub DAOInternetSync()
Dim dbsTemp As DAO.Database
Set dbsTemp = DBEngine.OpenDatabase(".\NewNorthWind.mdb")
' Synchronize the local database with the replica on
' the Internet server.
dbsTemp.Synchronize "sampleserver/files/Northwind.mdb", _
dbRepImpExpChanges Or dbRepSyncInternet
dbsTemp.Close
End Sub
JRO
Sub JROInternetSync()
Dim repMaster As New JRO.Replica
repMaster.ActiveConnection = ".\NorthWind.mdb"
' Synchronize the local database with the replica on
' the Internet server.
repMaster.Synchronize "sampleserver/files/Northwind.mdb", _
jrSyncTypeImpExp, jrSyncModeInternet
Set repMaster = Nothing
End Sub
For more information on Internet synchronization, read the article "Internet Synchronization with the Microsoft Jet Database Engine: A Technical Overview" available in the MSDN Library.
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 parameter that specifies jrSyncModeDirect. For functionality equivalent to DAO you must specify jrSyncModeDirect when calling the Synchronize method. In JRO if the SyncMode parameter is omitted, the synchronization will be performed indirectly. The ability to perform indirect synchronizations is a new feature in JRO designed to increase performance when synchronizing over a wide area network (WAN). See the section "New Features in ADO, ADOX, and JRO" for more information about performing indirect synchronizations.
The following table shows the mapping between the DAO Exchange parameter of the Synchronize method and the JRO SyncType and SyncMode parameters.
| DAO parameter | DAO constant | JRO parameter | JRO constant |
| Exchange | dbRepExportChanges | SyncType | jrSyncTypeExport |
| Exchange | dbRepImportChanges | SyncType | jrSyncTypeImport |
| Exchange | dbRepImExpChanges | SyncType | jrSyncTypeImpExp |
| Exchange | dbRepSyncInternet | SyncMode | jrSyncModeInternet |
If two users at 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 row level, a conflict will occur if two users make a change to the same record. If changes are being tracked at the column level, a conflict will occur if two users make a change to the same column with a 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 row by row, resolving the conflicts as appropriate.
The following listings demonstrate 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.
DAO
Sub DAOConflictTables()
Dim dbsNorthwind As DAO.Database
Dim tdfTest As DAO.TableDef
Dim bConflict As Boolean
Set dbsNorthwind = DBEngine.OpenDatabase(".\NorthWind.mdb")
bConflict = False
' Enumerate TableDefs collection and check ConflictTable
' property of each.
For Each tdfTest In dbsNorthwind.TableDefs
If tdfTest.ConflictTable <> "" Then
' There was a conflict with this table
Debug.Print tdfTest.Name & " had a conflict."
bConflict = True
End If
Next tdfTest
' If bConflict is still false then we didn't find any
' tables that had conflicts.
If Not bConflict Then Debug.Print "No conflicts."
dbsNorthwind.Close
End Sub
JRO
Sub JROConflictTables()
Dim repMaster As New JRO.Replica
Dim rstConflicts As ADODB.Recordset
repMaster.ActiveConnection = ".\NorthWind.mdb"
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
Do Until rstConflicts.EOF
Debug.Print rstConflicts.Fields(0) & " had a conflict."
rstConflicts.MoveNext
Loop
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 row for each table containing conflicts. With the ConflictTables property it is easy to determine whether conflicts occurred. If the Recordset is empty (the BOF and EOF properties of the Recordset are both true), no errors occurred. This differs from DAO in that 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.