Programming Microsoft Access 2000 (Microsoft Programming Series)

This section presents JRO development techniques for working with replication, including making a database replicable, creating full and partial replicas, compacting databases, synchronizing databases, and documenting replica properties.

Making a Database Replicable

You 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 discussed previously. 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

The connection string points at the database that you want to convert to a replicable format. You can set the Replicaobject'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 variable. Its default value is True. Recall that this can potentially help to 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 following routines apply the MakeReplicable method to the Northwind database as the database 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 variables 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. Then it 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. If present, this Boolean variable 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. Since 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.

NOTE

The procedure explicitly traps three distinct errors. One is for the operation of the FileSystemObject, and other two are from Jet. Notice that the replication component in Jet passes back the same Err number (-2147467259) for two distinctly different errors. Happily, these errors have different descriptions. The sample above 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.)

NOTE

Creating Additional Full Replicas

You 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, you 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, since 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:

The ReplicaName parameter specifies the path and filename of the new replica. It can be up to 255 characters long. Description is an optional field 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 a value of jrRepVisibilityGlobal (the default), jrRepVisibilityLocal, or jrRepVisibilityAnon. If you do not 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 following sample creates a replica based on the Northwind design master from the preceding sample. The ActiveConnection setting establishes this replica. 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."

You need not 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 does not 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 the same 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 of the data for a full replica. Recall that 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. In order 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, and then invoke the PopulatePartial method.

You can base a filter on the WHERE clause of a SQL statement (without the WHERE keyword) or on a relationship. You add and specify filters to a partial replica with the Filters collection's Append method. This method takes three arguments: The TableName property designates the table for which the filter specifies the content, the FilterType property denotes with a constant whether a SQL criteria statement (jrFilterTypeTable) or a relationship (jrFilterType (Relationship) filters entries for the table, and the FilterCriteria property 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 one is an object variable that points to the partial replica to repopulate. The second is a string variable that designates the path and filename 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 following four procedures illustrate one approach to defining two partial replicas by using 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". Then it repeats the process for another replica named "Partial of foo.mdb".

The makePartialFilter procedure accepts arguments from the procedure callMakePartialFilter that specify the name of the partial replica to create and the full replica data source for the partial replica. The makePartialFilter procedure also defines and appends the Filter objects for a partial replica, and it 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 variables it receives from the calling routine. Then it 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 fact that the file does not exist (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 filename, the key difference is that the make Partial procedure specifies jrRepTypePartial as the ReplicaType property while makeFullReplica uses jrRepTypeFull as its ReplicaType argument. Notice 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 that the full and new partial replica both reside in the same path. It is easy to remove this constraint; the chapter's final sample shows how to do this.

After the makePartial procedure returns control to the procedure make PartialFilter, 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

The following two procedures 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 and 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 and 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.

Working with Prevent Deletes Replicas

A Prevent Deletes replica is easy to create, but you must create it from the user interface. Choose Tools-Replication-Create Replica and select the Prevent Deletes check box in the dialog box that opens. This type of replica allows an application to distribute a replica that does not support direct deletes to its contents. While you can achieve this result using Access security settings, it is a lot easier to just select the check box. While the user of a Prevent Deletes replica cannot directly delete records, the replica can accept delete updates from other replicas. One use for this type of replica is to enable an administrator to make sure that records are not deleted until they are properly archived.

The following three procedures manipulate Prevent Deletes replicas with ADO. SynchAddToFoo2 adds a new employee to the foo2.mdb replica from the foo.mdb replica. The employee's name is Rick Dobson. The second procedure, TryToDeleteFromFoo2, attempts to delete the same record directly from the foo2.mdb replica. The error message in Figure 11-3 below shows how Access responds to the Execute command in the procedure. If you need to restrict additions as well as deletions, you can set the Updatability argument in the CreateReplica method to jrRepUpdReadOnly. You do not need the user interface to do this. The third procedure, synchFooFoo2ToDelete, removes Rick Dobson from the foo.mdb replica and then propagates that deletion to the foo2.mdb replica. Although users cannot delete a record directly from a Prevent Deletes replica, an administrator can propagate a deletion using synchronization.

Figure 11-3. A Prevent Deletes replica returns an error message like this one when you try to delete one of its records.

Working with Replica Properties

By examining replica properties, you can understand the behavior of a replica set. For example, if you know a replica's type, you know whether it will share schema changes with other replicas. A replica's Priority property indicates which replica wins when two replicas conflict with one another. In general, the replica with the higher priority value wins. The three procedures below print replica properties and expose property values in the process.

Since there is no Replicas collection, you are likely to list sequentially a number of replicas when you want to process them—especially if the replicas belong to different replica sets. The callPrintTypePriority procedure sets path and replicaName variables for each replica it wants to examine, and then it calls two subroutines. One subroutine (printReplicaType) returns information about the type of replica, and the other (printPriority) returns the Priority property value for a replica. In addition to path and replicaName, the set of procedures exchanges values with an argument named Exist. This Boolean variable tracks whether a file exists in a path. If a file does not exist, it obviously has no priority value.

The callPrintTypePriority procedure uses error trapping to determine whether a file exists and to respond appropriately if it does not. When it tries to set the ActiveConnection property of a replica instance to a file that does not exist, the Jet replication component returns its standard Err number (-2147467259) with a descriptive phrase. The error trap checks for the phrase with the Jet replication Err number. If the error-trap logic determines that the file does not exist, the trap sets Exist to False and resumes. The two subroutines, printReplicaType and printPriority, interpret the False value for Exist and respond appropriately.

The callPrintTypePriority procedure sends the ReplicaType property value for a replica when it calls the printReplicaType procedure. The printReplicaType procedure checks the value of Exist before trying to decipher the value of the ReplicaType property. If Exist is False, the procedure simply prints a message to the Immediate window that the file does not exist. If Exist is True, a Select Case statement decodes the ReplicaType property.

NOTE

The design of the printPriority procedure is slightly different from that of the printReplicaType procedure. The printPriority procedure creates a replica instance within it. Then it derives a property value for the replica. In addition to checking the value of the Exist function, it checks the ReplicaType property of the replica instance. If the property shows that the file is not replicable by returning a value of jrRepTypeNotReplicable, the procedure prints that there is no priority for the file. Otherwise, the procedure prints the value returned by the Priority property of its replica instance. Before returning control to the callPrintTypePriority procedure, printPriority resets the value of Exist to True for processing the next replica.

Compacting and Encrypting Replicas

The JRO library supports more than just replication. For example, you can compact and encrypt files and you can refresh the memory cache. The following two procedures make a backup of a replica by compacting it and encrypting the compacted copy. This approach is particularly appropriate when you send a file with sensitive information over the Internet. The sample compacts the Northwind.mdb file to Northwind2.mdb. You can optionally specify separate paths for Northwind and Northwind2.