JRO Development Techniques
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
Replica.MakeReplicable ConnectionString, ColumnTracking |
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.
Sub callMakeDesignMaster() path = "C:\Program Files\Microsoft Office\" & _ "Office\Samples\" replicaName = "Northwind.mdb" 'Set the second parameter to True to invoke 'column-level tracking of resolution conflicts. makeDesignMaster path & replicaName, True End Sub Sub makeDesignMaster(newReplica As String, _ Optional ColumnTracking As Boolean) On Error GoTo DMTrap Dim repMaster As New JRO.Replica 'Offer to copy database for restoring it after 'making the database replicable. If MsgBox("Do you want to make a backup copy", _ vbYesNo, "Programming Microsoft Access 2000") = 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 Microsoft Access 2000" 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 Microsoft Access 2000" Resume DMExit ElseIf Err.Number = 53 Then MsgBox "Original file not found for backup copy. " & _ "Correct file name and try again.", vbCritical, _ "Programming Microsoft Access 2000" Resume DMExit Else Debug.Print Err.Number; Err.Description End If End Sub |
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
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 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
You can differentiate native errors from Jet errors without relying on the Err Description property. However, this 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
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:
Replica.CreateReplica ReplicaName, Description, ReplicaType, _ Visibility, Priority, Updatability |
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."
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" 'Make sure foo.mdb is deleted before running the next line. repMaster.CreateReplica "c:\My Documents\foo.mdb", _ "foo full replica", jrRepTypeFull, _ jrRepVisibilityGlobal, , jrRepUpdFull End Sub |
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".
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 that 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 |
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.
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 'When it comes to learning about computers and my faith 'in the Lord, I am always newly born. 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 |
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.
Sub SynchAddToFoo2() Dim rep1 As JRO.Replica Dim cnn1 As New ADODB.Connection Dim rst1 As ADODB.Recordset '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 'Add a new employee to foo. 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 foo with foo2.mdb. rep1.Synchronize "c:\My Documents\foo2.mdb", _ jrSyncTypeImpExp, jrSyncModeDirect End Sub Sub TryToDeleteFromFoo2() Dim rep As JRO.Replica Dim cnn1 As New ADODB.Connection, cmd1 As ADODB.Command 'Open connection to foo2 and 'set a reference to it as a replica. cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\My Documents\foo2.mdb" Set rep1 = New JRO.Replica rep1.ActiveConnection = cnn1 'Execute command to remove employee from foo2.mdb; 'it fails because Foo2 is a Prevent Deletes replica. Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = cnn1 .CommandText = "DELETE Employees.* FROM Employees" & _ " WHERE LastName='Dobson'" .CommandType = adCmdText .Execute End With End Sub Sub synchFooFoo2ToDelete() 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:\My Documents\foo2.mdb", _ jrSyncTypeImpExp, jrSyncModeDirect End Sub |
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.
Sub callPrintTypePriority() On Error GoTo TypeTrap Dim repMaster As New JRO.Replica Dim path As String, replicaName As String Dim Exist As Boolean 'Assign Boolean for file existing. Exist = True 'Assign path and replica names, then 'call procedures for printing type and priority. path = "C:\Program Files\Microsoft Office\" & _ "Office\Samples\" replicaName = "Northwind.mdb" 'If file does not exist, the next line catches it. repMaster.ActiveConnection = path & replicaName printReplicaType replicaName, repMaster.ReplicaType, Exist printPriority replicaName, path, Exist replicaName = "Copy of Northwind.mdb" repMaster.ActiveConnection = path & replicaName printReplicaType replicaName, repMaster.ReplicaType, Exist printPriority replicaName, path, Exist replicaName = "Northwind2.mdb" repMaster.ActiveConnection = path & replicaName printReplicaType replicaName, repMaster.ReplicaType, Exist printPriority replicaName, path, Exist path = "C:\My Documents\" replicaName = "foo.mdb" repMaster.ActiveConnection = path & replicaName printReplicaType replicaName, repMaster.ReplicaType, Exist printPriority replicaName, path, Exist path = "C:\Program Files\Microsoft Office\" & _ "Office\Samples\" replicaName = "Partial of Northwind.mdb" repMaster.ActiveConnection = path & replicaName printReplicaType replicaName, repMaster.ReplicaType, Exist printPriority replicaName, path, Exist path = "C:\My Documents\" replicaName = "Partial of foo.mdb" repMaster.ActiveConnection = path & replicaName printReplicaType replicaName, repMaster.ReplicaType, Exist printPriority replicaName, path, Exist replicaName = "DMBackup.mdb" repMaster.ActiveConnection = path & replicaName printReplicaType replicaName, repMaster.ReplicaType, Exist printPriority replicaName, path, Exist path = "C:\My Documents\" replicaName = "foo2.mdb" repMaster.ActiveConnection = path & replicaName printReplicaType replicaName, repMaster.ReplicaType, Exist printPriority replicaName, path, Exist TypeExit: Exit Sub TypeTrap: If Err.Number = -2147467259 And _ Left(Err.Description, 19) = _ "Could not find file" Then Exist = False Resume Next Else Debug.Print Err.Number, Err.Description Resume TypeExit End If End Sub Sub printReplicaType(repName As String, _ typeNumber As Integer, Exist As Boolean) If Exist Then 'Decode replica type enumeration constants or... Select Case typeNumber Case jrRepTypeNotReplicable Debug.Print repName & " is not replicable." Case jrRepTypeDesignMaster Debug.Print repName & " is a Design Master." Case jrRepTypeFull Debug.Print repName & " is a Full Replica." Case jrRepTypePartial Debug.Print repName & " is a Partial Replica." End Select Else 'print that file does not exist. Debug.Print repName & " does not exist." End If End Sub Sub printPriority(replicaName As String, path As String, _ Exist) Dim repMasterP As New JRO.Replica 'Print priorty and reset Exist. If Exist = True Then 'Assign connection for replica. repMasterP.ActiveConnection = path & replicaName If repMasterP.ReplicaType <> jrRepTypeNotReplicable Then 'Print priority for replicas. Debug.Print "It's priority is " & repMasterP.Priority & "." Else 'Print message for no replica. Debug.Print "Therefore, it has no priority." End If Else 'Print message for file does not exist. Debug.Print "Therefore, it has no priority." End If Debug.Print Exist = True End Sub |
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 printReplicaType procedure represents ReplicaType values with the JRO ReplicaTypeEnum constant names. You can view these with the Object Browser. Enter ReplicaType in the Search text box and click the Search button. Select from the return set the ReplicaTypeEnum class for the JRO library. (Be careful: The DAO library has a different ReplicaTypeEnum class.) This selection lists the constant names and their ReplicaType values.
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.
Sub callCompactADB() compactADB "Northwind.mdb", "Northwind2.mdb", _ "C:\Program Files\Microsoft Office\Office\Samples\" End Sub Sub compactADB(oName As String, cName As String, _ opath As String, Optional cpath As String) Dim je As New JRO.JetEngine Dim strIn As String, strOut As String 'Is optional path specified? If cpath = "" Then cpath = opath End If strIn = opath & oName strOut = cpath & cName deleteFile strOut je.CompactDatabase _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strIn, _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strOut & ";" & _ "Jet OLEDB:Encrypt Database=True" End Sub |
EAN: 2147483647
Pages: 97