This is the final installment in a four-part series on ADO in Access 2000. Earlier installments introduced ADO, and illustrated database manipulation capabilities and data definition functions. This installment focuses on database replication. By tapping this feature, developers can present separate copies - replicas - of databases to different groups of users, while managing the replicas as a single database. This capability is particularly important for high-end Access applications with many users, or with a few users who need exclusive database access while others read or update the database. Database replication also adds value to projects in which branch offices or mobile workers need to share parts of a database with a complete version at headquarters. Data can be shared without the need for a constantly open connection between two points.
This article assumes a working knowledge of database replication. (See Chapter 11 of my book Programming Microsoft Access 2000 [Microsoft Press, 1999] for a general summary of Access 2000 database replication. This article is based on excerpts from that book.) Two preliminary sections in this article offer an overview of the Microsoft Jet and Replication Objects 2.1 Library (JRO) and what's new with Access 2000 replication. After you set a reference to the JRO library, your applications can control database replication via JRO. The remainder of the article reviews code samples that illustrate basic JRO techniques for managing the replication process.
The JRO Model
JRO programming is fundamentally an ADO approach because it relies on ADO Connection objects. While ADO is a universal data access technology, the JRO extension functions exclusively with the Jet database engine. If you programmed custom replication solutions in Access 95/97, now is the time to transition from DAO to JRO programming.
The JRO model supports three general tasks: creating and managing replica sets; compacting and encrypting databases; and refreshing the memory cache to improve apparent performance. The three main objects in the JRO model are shown in FIGURE 1. The first is the Jet Engine object, which supports features specific to the Jet database engine, including compacting and encrypting databases, and refreshing the memory cache.
FIGURE 1: An overview of the JRO library command object.
The second main object is the Replica object, which represents a copy of a database. Replica objects are the basic building block of a replica set. You can manage a replica set by manipulating the properties and methods of the Replica objects. Among the functions you can administer are:
The JRO model also includes properties for managing many new and revised features, such as visibility, replica type, and priority-based conflict resolution.
The third main object is the Filter object. Use this with partial replicas to restrict the contents of a database copy. You can base a filter on a table or a relationship. JRO has a Filters collection object for all the filters in a replica. These collectively limit the data that can enter into a partial replica.
What's New in Access 2000 Replication
One of the more significant upgrades in Access 2000 is the introduction of the JRO model for programmatically controlling replication. Because some new features, such as the Visibility property, aren't available with the traditional DAO programmatic interface - even in its latest upgrade, version 3.6 - you should definitely learn the new way of programming replication. This section briefly explains selected replication innovations introduced by the latest release of Access.
Access 2000 offers bi-directional replication between Jet and Microsoft SQL Server replicas. This kind of transfer requires Jet 4 and Microsoft SQL Server 7. Bi-directional transfer means that SQL Server can serve as a central repository for a disconnected set of Access applications. Mobile workers with Access applications can transfer updates to a central database, and download the latest changes from a headquarters database. You need to start with a SQL Server replica, or upsize an Access replica to SQL Server.
When you set up a SQL Server-Jet replica set, a SQL Server replica must be at the hub, and Jet replicas can function at the spokes. The Jet replicas can exchange content bi-directionally with the SQL Server hub replica. However, Jet replicas cannot exchange content bi-directionally with other Jet replicas at the spokes. The SQL Server hub replica must always serve as an intermediary between Jet replicas at the spokes. Because SQL Server is simply a database engine and not a full application-development environment like Access, you cannot replicate Access-specific application objects, such as forms and reports, to the SQL Server hub.
Access 2000 introduces column-level updates to minimize collisions between two replicas. With prior Access versions, updates from two replicas collided if they changed the same record - even if they changed two different fields on the same record - because the lowest level of update tracking for replicas was the row. With Access 2000, you can create replicas that detect changes down to the level of individual fields. Therefore, one user can change a customer's fax number in one replica, while another user changes the street address of the contact person for the same customer in another replica. When the two replicas synchronize, no conflicts result.
Access 2000 introduces three degrees of visibility for replicas: global, local, and anonymous. You can control the visibility of a replica as a property in JRO at the time you initially create a replica. You cannot change the visibility of a replica after you create it.
Replicas with globalvisibility function just as traditional Access replicas. They can replicate with any other replica, and they are visible throughout a replica set. Local replicas are visible only to their parent, and they exchange content exclusively with their parent. Either the parent or the local replica can initiate an exchange. In addition, the hub parent for a local replica can schedule recurring synchronizations with a local replica. Anonymous replicas are for distribution across a Web-based (FTP or HTTP 1.1) connection. For successful synchronization of an anonymous client replica with a Web-based hub replica, the original anonymous replica must have as its source a global replica managed by the Replication Manager on the Web server.
Access 2000 has a new default conflict resolution rule. The earlier releases of Access resolved conflicts between two replicas in favor of the replica that changed a record the most. If two replicas changed a record an equal number of times, the replica with the lowest ReplicaId property won. The new default conflict resolution scheme employs a variation of the 800-pound gorilla rule: The replica with the highest priority wins. Priority property settings for replicas can range from 0 to 100. Again, if two replicas have an equal priority, the one with the lowest ReplicaId property wins. This new rule has the advantage of being consistent with SQL Server 7.
Making a Database Replicable
Use the MakeReplicable method of a Replica object to make a database replicable. This process results in the new replica becoming a design master with a global Visibility property setting. Converting a standard Access database to a replicable database adds the special fields and tables. These additional fields and tables can increase the size of a database substantially, so you might want to make a backup copy of your database before making it replicable.
The syntax for the MakeReplicable method is:
Replica.MakeReplicable ConnectionString, ColumnTracking
The connection string points at the database you want to convert to a replicable format. You can set the Replica object's ActiveConnection property before invoking the MakeReplicable method. However, the connection string argument for the method overrides the ActiveConnection property for a replica. The ColumnTracking argument for the method is a Boolean with a default value of True. Recall that this can potentially help reduce synchronization conflicts. If conflicts are unlikely (for example, because all the editing will take place with one replica), you should consider setting the value to False. Synchronization conflict tracking then falls back to the traditional row-level tracking. This eliminates a performance hit associated with column-level tracking.
The routines in Listing One apply the MakeReplicable method to the Northwind database as it performs backup and error-trapping functions. The callMakeDesignMaster procedure assigns values to its path and replicaName arguments before calling the makeDesignMaster procedure. The sample concatenates path and replicaName as it calls the second procedure. The second procedure invokes the MakeReplicable method for the values passed to it.
Before invoking the MakeReplicable method, the makeDesignMaster procedure asks whether the user wants to make a backup copy. If the user responds "Yes," the procedure creates an instance of the FileSystemObject. It then invokes the Copyfile method to back up the file. This makes it easy to return to a version of the database without the special replication fields and tables.
The makeDesignMaster procedure accepts up to two arguments. The first argument is the concatenation of path and replicaName. The design master for the new replica set is a file named by the value for replicaName. The second procedure optionally accepts a second argument, a Boolean, that specifies whether to invoke column-level tracking of synchronization conflicts. The sample passes a value of True. The sample's design requires the user to specify True for the second argument to obtain column-level tracking. Failure to specify the second argument causes the Boolean variable to assume its default value of False. Because the MakeReplicable method creates replicas with column-level tracking by default, the procedure does not actually have to specify True to create a replica with this feature. There is no column-level tracking property. Therefore, you must manually track the status of this variable for all your replicas.
The procedure explicitly traps three distinct errors. One is for the operation of the FileSystemObject, and the other two are from Jet. Notice that the replication component in Jet passes back the same Err number (-2147467259) for two different errors. Happily, these errors have different descriptions. The previous sample uses this feature to distinguish between the two. A production system would use a longer description segment to identify the error type definitely, or use a more advanced technique for parsing errors. You can also differentiate native errors from Jet without relying on Err.Description. However, the Description property is potentially meaningful to more developers. The other technique involves enumerating the Errors collection of the Connection object. It returns distinct error numbers from the native database engine.
Creating Additional Full Replicas
Apply the CreateReplica method to a new instance of a Replica object to transform the instance into a new member of a replica set. Before invoking the method, assign the ActiveConnection property for the new instance so that it points at the design master or another replica from the target replica set. This method fails if the ActiveConnection setting inadvertently denotes a database with a ReplicaType property of jrRepTypeNotReplicable. In general, this method returns a new replica of the same type and visibility as the model. However, because there should be only one design master, modeling a new replica on a design master returns another global replica subject to the parameters for the method. The general syntax for the application of the method is:
Replica.CreateReplica ReplicaName, Description, _
ReplicaType, Visibility, Priority, Updatability
The ReplicaName parameter specifies the path and file name of the new replica. It can be up to 255 characters long. Description is an optional argument that helps identify members in a replica set. The default ReplicaType value is jrRepTypeFull for a full replica. You can specify jrRepTypePartial instead. The Visibility parameter can have values of jrRepVisibilityGlobal (the default), jrRepVisibilityLocal, and jrRepVisibilityAnon. If you don't specify a value for Priority, it uses its default rules; the maximum range is 0 through 100. A full replica has 90 percent of its parent's Priority value by default. The Updatability parameter can designate either a read-only replica (jrRepUpdReadOnly) or a read-write replica (jrRepUpdFull).
The sample in FIGURE 2 creates a replica based on the Northwind design master from the preceding sample. The ActiveConnection setting establishes this. The new replica is a full one with global visibility. The path for the replica is C:\My Documents\foo.mdb. In accordance with the parameter settings for the new replica, its description is "foo full replica."
Sub makeFullReplica()
Dim repMaster As New JRO.Replica
' Point repMaster at a Design Master mdb.
repMaster.ActiveConnection = _
"C:\Program
Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb"
' Ensure foo.mdb is deleted before running next line.
repMaster.CreateReplica
"c:\My Documents\foo.mdb", _
"foo full
replica", jrRepTypeFull, _
jrRepVisibilityGlobal,
, jrRepUpdFull
End Sub
FIGURE 2: Using the CreateReplica method of the Replica object.
You don't need to create a backup in this case, because the command creates a new replica that must be based on an existing replica. However, the procedure can fail if the replica already exists, or if the model for the replica doesn't exist (that is, if the file is missing). These are simple error-trapping issues. The initial sample took one approach to this kind of task while creating a replica. You might want to assume that the new version makes any existing version with that name obsolete. The second sample implements that logic by deleting the old replica, which eliminates the source of one error before it arises.
Creating Partial Replicas and Filters
A partial replica is a replica with less than all the data for a full replica. This type of replica is useful for branch offices and mobile workers who need access to a subset of the data maintained at headquarters. Using partial replicas limits the amount of data that the users of a replica can view, and it reduces the amount of updating necessary to synchronize a replica.
After you make a blank partial replica with the CreateReplica method, you must populate the partial replica with data. Each partial replica has a Filters collection. Each Filter object within the Filters collection specifies a different slice of data that the partial replica contains. For a partial replica to contain data initially, you must specify one or more filters for it, append these to the replica's Filters collection, then invoke the PopulatePartial method.
You can base a filter on the WHERE clause of a SQL statement (without the WHERE keyword) or a relationship. Add and specify filters to a partial replica with the Filters collection's Append method. This method takes three arguments: TableName designates the table for which the filter specifies the content; FilterType denotes with a constant whether a SQL criteria statement (jrFilterTypeTable) or a relationship (jrFilterTypeRelationship) filters entries for the table; and FilterCriteria includes the relationship name or the WHERE phrase from a SQL statement that delimits the records for a table.
The PopulatePartial method for a replica clears all records in a partial replica, and repopulates the replica based on its current filters. It does this by synchronizing the partial replica with a full replica. The method takes two arguments: the first is an object variable that points to the partial replica to repopulate; the second is a string variable that designates the path and file name for the full replica with which the partial replica synchronizes. You should generally use the PopulatePartial method for a partial replica when you initialize the replica or change its filters. To use the PopulatePartial method with a partial replica, you must first open the replica with exclusive access because the method removes all records from the replica as the first step to repopulating the replica with records.
The four procedure listings in Listing Two illustrate one approach to defining two partial replicas with Filter collections and the PopulatePartial method. The callMakePartialFilter procedure calls makePartialFilter twice with two different sets of arguments. First, it launches the process to create a partial replica named "Partial of Northwind.mdb." It then repeats the process for another replica named "Partial of foo.mdb."
The makePartialFilter procedure accepts arguments from the callMakePartialFilter procedure that specify the name of the partial replica to create, and the full replica that serves as the source for the partial replica. The makePartialFilter procedure also defines and appends the Filter objects for a partial replica, and invokes the PopulatePartial method to apply the filters.
The procedure first deletes the name of any existing partial replica with the same name and location as the one it wants to create. It does this by defining a string variable based on the path and replicaName parameters it receives from the calling routine. It then passes that new string variable to the deleteFile procedure. Barring an unanticipated error, deleteFile performs one of three tasks: it deletes the old file for the replica; it reminds a user to close a replica so that the application can delete it; or it ignores an error caused by the file not existing (error number 53).
After attempting to delete the existing file, the procedure creates a new partial replica by calling the makePartial procedure. The call passes three arguments: path, replicaName, and sourceName. The makePartial procedure is nearly identical in design to the makeFullReplica procedure. Both invoke the CreateReplica method for a new instance of a Replica object. Aside from using variables to denote the path and file name, the essential difference is that the makePartial procedure specifies jrRepTypePartial as ReplicaType, while makeFullReplica uses jrRepTypeFull. Note that makePartial specifies the new replica's name as the concatenation of the path and replicaName variables. The ActiveConnection property for the new replica instance specifies the full replica source for the partial replica. The procedure specifies this source as the concatenation of the path and sourceName variables. It requires the full and new partial replica both reside in the same path.
After the makePartial procedure returns control to the makePartialFilter procedure, the new partial replica exists, but has no data. So makePartialFilter populates it with data. First, it sets the ActiveConnection of a replica instance to the new partial replica, and it opens the replica in exclusive mode. Recall that this is necessary for the application of the PopulatePartial method. Next, it defines and appends a couple of Filter objects to the replica. The first filter extracts sales representatives from the Employees tables. The second filter extracts customers from Madrid, Spain. Finally, the PopulatePartial method synchronizes the full replica denoted by the concatenation of path and sourceName with the new partial replica. Only two tables receive records. (You can add filters to populate more tables in the partial replica.)
Synchronizing Replicas
Listing Three lists two procedures that illustrate how to synchronize replicas in typical replication scenarios. Both use basic ADO procedures. The synchNorthwindFooToAdd procedure adds a new record to the Employees table in the Northwind.mdb replica. This is the design master for a replica set that includes foo.mdb. The procedure then synchronizes Northwind with foo to propagate the new record to foo.mdb. The second procedure, synchFooNorthwindToDelete, deletes the new employee record from foo, then synchronizes foo with Northwind to remove the record from the Northwind replica as well.
The synchNorthwindFooToAdd procedure uses ADO to add a record to a table in one replica, then propagate that record to a corresponding table in another replica. It starts by declaring Replica, Connection, and Recordset objects. Then it opens a connection to the Northwind database and sets the ActiveConnection property of the replica with the connection. It creates an instance of a Recordset object on the same connection as the replica. Then it adds an employee named "Rick Dobson." The procedure closes by applying the Synchronize method to the Northwind replica and naming foo as the replica with which to exchange updates. This final step passes the new employee record from Northwind to foo.
The synchFooNorthwindToDelete procedure removes the new record from foo. It also restores Northwind by synchronizing with it. This procedure uses a Command object to drop an employee with a last name of "Dobson" from the Employees table in foo. After executing the command, it applies the Synchronize method to the foo replica to propagate the delete to Northwind.
Conclusion
The magic of database replication is that you can integrate the changes in multiple replicas. Database replication achieves this without copying over the changes in any replica. The synchronization sample shows you how to programmatically launch this. It's likely that a database administrator will need to periodically reconcile the replicas to resolve replication conflicts and errors that do not resolve through automatic procedures. Manual and programmatic procedures can facilitate this process. Even if you do ultimately fully reconcile conflicts programmatically, you're likely to want to initially perform manual resolution to clarify the data issues that require resolution. One programmatic solution to conflict resolution involves replacing the built-in Conflict Resolution Wizard - a formidable task.
Rick Dobson, Ph.D. is president of CAB, Inc. CAB offers Office/Web/database services, as well as support for Office-based automated accounting packages. Rick is also the author of Programming Microsoft Access 2000 [Microsoft Press, 1999]. Visit the CAB Web site at http://www.cabinc.net for samples, presentations, full-length articles, and prizes.
Begin Listing One - MakeReplica
Sub callMakeDesignMaster()
Path = _
"C:\Program
Files\Microsoft Office\Office\Samples\"
Path = "C:\My
Documents\"
replicaName =
"Northwind.mdb"
'
Set the second parameter to True to invoke
'
column-level tracking of resolution conflicts.
makeDesignMaster Path
& replicaName
End Sub
Sub makeDesignMaster(newReplica As String, _
Optional
ColumnTracking As Boolean)
On
Error GoTo
DMTrap
Dim
repMaster As New
JRO.Replica
'
Offer to copy of database for restoring it after
'
making the database replicable.
If
MsgBox("Do you want to make a backup copy", _
vbYesNo, "Programming
MS Access") = vbYes Then
Set fs = CreateObject("Scripting.FileSystemObject")
fs.Copyfile
newReplica, "c:\My Documents\DMBackup.mdb"
End
If
'
Optionally make the newReplica database replicable.
If
ColumnTracking = True Then
repMaster.MakeReplicable
newReplica
Else
repMaster.MakeReplicable newReplica, False
End
If
'
Clear reference to Design master.
Set
repMaster = Nothing
DMExit:
Exit Sub
DMTrap:
If
Err.Number = -2147467259 And _
Left(Err.Description,
5) = "Could" Then
MsgBox "Can not
create replica because file does " & _
"not exist. Fix
path/file name and try again.", _
vbCritical,
"Programming MS Access"
Resume DMExit
ElseIf
Err.Number = -2147467259 And _
Left(Err.Description, 8)
= "Database" Then
MsgBox "Database
is already replicable. Use the "
& _
"CreateReplica
method to base a new replica " & _
"on it.",
vbCritical, "Programming MS Access"
Resume DMExit
ElseIf
Err.Number = 53 Then
MsgBox "Original
file not found for backup copy. "
& _
"Correct file
name and try again.", vbCritical, _
"Programming MS
Access"
Resume DMExit
Else
Debug.Print
Err.Number; Err.Description
End
If
End Sub
End Listing One
Begin Listing Two - Four samples
Sub callMakePartialFilter()
makePartialFilter
"Partial of Northwind.mdb", _
"Northwind.mdb", "C:\Program Files\" & _
"Microsoft
Office\Office\Samples\"
makePartialFilter
"Partial of foo.mdb", _
"foo.mdb",
"C:\My Documents\"
End Sub
Sub makePartialFilter(replicaName
As String, _
sourceName As String, path As String)
Dim
rep As New
JRO.Replica
Dim
flt1 As JRO.Filter
'
Delete old partial.
strfile = path &
replicaName
deleteFile (strfile)
'
Make Partial.
makePartial path,
replicaName, sourceName
'
Open connection to Partial and append filter.
rep.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & replicaName
& ";Mode=Share Exclusive"
rep.Filters.Append
"Employees", jrFilterTypeTable, _
"Title=' Sales
Representative' "
rep.Filters.Append
"Customers", jrFilterTypeTable, _
"Country=' Spain'
AND City=' Madrid' "
'
Populate Partial from source.
rep.PopulatePartial path
& sourceName
End Sub
Sub deleteFile(strfile)
On
Error GoTo
deleteTrap
Dim
cnn1 As New
ADODB.Connection
'
Prepare to delete file.
Set
fs = CreateObject("Scripting.FileSystemObject")
fs.deleteFile strfile
deleteExit:
Exit
Sub
deleteTrap:
If
Err.Number = 70 Or Err.Number = 75 Then
MsgBox "Partial
is unavailable to system. " & _
"Close it so
the system can create a new one.", _
vbCritical,
"Programming Microsoft Access 2000"
ElseIf
Err.Number = 53 Then
Resume Next
Else
Debug.Print
Err.Number; Err.Description
End
If
Resume
deleteExit
End Sub
Sub makePartial(path As String,
replicaName As String,
_
sourceName As String)
Dim
rep As New
JRO.Replica
rep.ActiveConnection =
path & sourceName
rep.CreateReplica path
& replicaName, replicaName, _
jrRepTypePartial,
jrRepVisibilityGlobal, , jrRepUpdFull
End Sub
End Listing Two
Begin Listing Three - synchNorthwindFooToAdd
Sub synchNorthwindFooToAdd()
Dim
rep1 As JRO.Replica
Dim
cnn1 As New
ADODB.Connection
Dim
rst1 As ADODB.Recordset
'
Open connection to Northwind and set reference for
'
Northwind as a replica.
cnn1.Open
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb"
Set
rep1 = New JRO.Replica
rep1.ActiveConnection =
cnn1
'
Add a new employee to Northwind.
Set
rst1 = New ADODB.Recordset
rst1.Open
"Employees", cnn1, adOpenKeyset, _
adLockOptimistic,
adCmdTable
rst1.AddNew
rst1.Fields("FirstName") = "Rick"
rst1.Fields("LastName") = "Dobson"
rst1.Fields("BirthDate")
= Date - 1
rst1.Update
'
Synchronize Northwind with its full replica (foo.mdb).
rep1.Synchronize
"c:\My Documents\foo.mdb", _
jrSyncTypeImpExp,
jrSyncModeDirect
End Sub
Sub synchFooNorthwindToDelete()
Dim
rep1 As JRO.Replica
Dim
cnn1 As New
ADODB.Connection, cmd1 As ADODB.Command
'
Open connection to foo and set reference
'
to foo as a replica.
cnn1.Open
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\My Documents\foo.mdb"
Set
rep1 = New JRO.Replica
rep1.ActiveConnection =
cnn1
'
Execute command to remove an employee from foo.mdb.
Set
cmd1 = New ADODB.Command
With
cmd1
.ActiveConnection =
cnn1
.CommandText =
"DELETE Employees.* FROM Employees" & _
"
WHERE LastName=' Dobson' "
.CommandType =
adCmdText
.Execute
End
With
'
Synchronize foo with its Design Master (Northwind.mdb).
rep1.Synchronize
"c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb", jrSyncTypeImpExp, _
jrSyncModeDirect
End Sub
End Listing Three
Copyright © 1999 Informant Communications Group. All Rights Reserved. • Send feedback to the Webmaster • Important information about privacy