JRO

[Previous] [Next]

JRO includes Jet database replication features and provides the ability to compact Jet databases and refresh the data in the Jet engine's cache. The JRO object model includes three main objects: the JetEngine object, the Replica object, and the Filter object. First I'll cover the JetEngine object, and later in this appendix I'll discuss the Replica object. Version 2.5 of the JRO objects can be accessed in Visual Basic by selecting Microsoft Jet And Replication Objects 2.5 Library in the References dialog box.

JetEngine Object

The JRO JetEngine object exposes two methods—CompactDatabase and RefreshCache—that provide the ADO and Jet programmer with features previously available only through the DAO DBEngine object. Let's take a brief look at each of these methods.

CompactDatabase method

When you delete a row in an Access table, that row is "lost"—in other words, you can no longer view the row. However, that data is not removed from the physical database file. As a result, continued use of an Access database causes the file to grow.

Access has the ability to "compact" a database. When you compact a database, you recover its lost space. The Jet engine essentially copies the database's structure and the contents of its tables into a new copy of the database. The engine does not copy deleted rows. Thus, the new copy of the database is smaller than the original copy that contained deleted rows.

The CompactDatabase method also allows you to control attributes of the new database, including its format (Access 2000, Access 97, and so on), database password, and locale. For more information on each of these options, see the documentation on the CompactDatabase method in the Jet and Replication Objects portion of the Platform SDK.

Here's an example that uses CompactDatabase to compact a database:

 strConnOriginal = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=D:\Program Files\MyApp\Orig.MDB;" strConnNew = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=D:\Program Files\MyApp\New.MDB;" Set jroEngine = New JRO.JetEngine jroEngine.CompactDatabase strConnOriginal, strConnNew 

RefreshCache method

Before I describe the RefreshCache method of the JetEngine object, let me first discuss an extremely important concept about the Jet architecture—in my estimation, a concept that eludes too many Jet programmers.

You can build a database application and have multiple users simultaneously reading from and writing to your database. Figure A-2 shows multiple clients accessing a data server. Applications that use SQL Server and Oracle databases employ this type of architecture regardless of whether the application uses a traditional two-tiered or an n-tiered approach. While the database engine caches data to improve performance by decreasing the number of times it communicates with the hard drive, all clients receive their data from the same database engine. The important point to take away from Figure A-2 is that all clients access data through the same database engine.

click to view at full size.

Figure A-2 Multiple clients accessing a database through a single database engine.

Multiuser Jet database applications look a little different, regardless of whether you use DAO or ADO. Each client loads its own copy of the Jet engine into its process. Each copy of the Jet engine reads and writes to the database file, as shown in Figure A-3.

click to view at full size.

Figure A-3 Multiple clients accessing a Jet database.

There's more than a simple structural difference between the Jet architecture and that of a central database engine such as SQL Server or Oracle. In the Jet architecture, each copy of the Jet engine caches data. When you ask the Jet engine to modify a row of data, that change is initially made only in the Jet engine's cache. The change is not written to the database file until later.

Say you add a row to a table in an Access database. Other users won't see that row until your copy of the Jet engine flushes its cache and writes the new row to the database file. You can force the Jet engine to write changes to the database file by wrapping them in a transaction. For more information on the intricacies of the Jet engine's cache and how you can fine-tune its behaviors, see the Microsoft Jet Database Engine Programmer's Guide, Second Edition.

Armed with some knowledge of the Jet architecture, let's move on to the RefreshCache method. When you run a query against an Access database, the Jet engine caches some data. A change made by another user that has been written to the Access database file after you've run your query might not be visible even after you requery your Recordset object. Here's an example:

 'Connect to your database. Set cn = New ADODB.Connection cn.Open strConn 'Create a table for testing purposes. cn.BeginTrans strSQL = "CREATE TABLE CacheTest (ID int " & _ "CONSTRAINT PK_CacheTest PRIMARY KEY, Description varchar(20))" cn.Execute strSQL, , adCmdText + adExecuteNoRecords 'Insert a row into the new table. strSQL = "INSERT INTO CacheTest (ID, Description) " & _ "VALUES (1, 'First Record')" cn.Execute strSQL, , adCmdText + adExecuteNoRecords 'Force the Jet engine to write the new row and table to the database file. cn.CommitTrans 'Run a query to check the number of rows in the table. strSQL = "SELECT COUNT(*) FROM CacheTest" Set rs = cn.Execute(strSQL, , adCmdText) Debug.Print rs(0) 'Use another connection to simulate a second user adding ' a row to the same table. Set cnOtherUser = New ADODB.Connection cnOtherUser.Open strConn cnOtherUser.BeginTrans strSQL = "INSERT INTO CacheTest (ID, Description) " & _ "VALUES (2, 'Second Record')" cnOtherUser.Execute strSQL, , adCmdText + adExecuteNoRecords 'Force the Jet engine to write the new row to the database file. cnOtherUser.CommitTrans 'Requery to determine the current number of rows in the table. rs.Requery Debug.Print rs(0) 

Although this code forces the Jet engine to write the row added by the second user to the database file, a requery of the Recordset object does not see the second row. To force the Jet engine to refresh the data in its cache, use the JetEngine object's RefreshCache method:

 'Force the Jet engine to write the new row to the database file. cnOtherUser.CommitTrans 'Force the Jet engine to refresh the cache on the main connection. Set jroEngine = New JRO.JetEngine jroEngine.RefreshCache cn 'Requery to determine the current number of rows in the table. rs.Requery Debug.Print rs(0) 

Now, when the code requeries the Recordset, you'll see the new row in the table.

Jet Replication

Replication is a process by which a database is duplicated, usually on different machines, and synchronized when changes are made. Like Jet security, Jet replication is a topic that we can't cover in nearly enough detail here. To develop a strong understanding of Jet replication, I highly recommend reading Chapter 7, "Database Replication," of the Microsoft Jet Database Engine Programmer's Guide, Second Edition. Consider this portion of the appendix an overview that will make the material in Chapter 7 of the programmer's guide a little easier to digest.

When you mark a database for replication, the Jet engine creates a number of system tables that track which changes must be replicated to other databases. To mark a database as "replicable," create a JRO Replica object and call its MakeReplicable method, as shown here:

 Set jroReplica = New JRO.Replica jroReplica.MakeReplicable strPathToMasterMDB, False 

The first parameter specifies the location of the database you want to mark as replicable. The second parameter controls the granularity of the tracking of changes. The default value for this parameter, which is True, indicates that you want to track changes by each column. This way, if two users change the same row but don't modify the same column in that row, both changes will succeed when replicated. A value of False would generate a conflict in the same scenario, similar to an optimistic updating conflict with Recordsets that use the ADO Cursor Engine's batch updating features.

You might not want all objects in a database to participate in replication. To control whether an object will participate in replication, call the Replica object's SetObjectReplicability method. The following code specifies that the table named LocalTable will not be replicated:

 jroReplica.SetObjectReplicability "LocalTable", "Tables", False 

The first parameter of the method specifies the object, and the second parameter specifies the type of object you want to control. The third parameter accepts a Boolean value that denotes whether you want the object to participate in replication. The Replica object also has a GetObjectReplicability method that you can use to programmatically determine whether an object in the database is targeted for replication. This method's syntax, shown in the following code, is nearly identical to the syntax of SetObjectReplicability:

 MsgBox jroReplica.GetObjectReplicability("LocalTable", "Tables") 

Once you've set the "replicability" of all the objects in the master database, you can create the replica databases by calling the CreateReplica method of the Replica object:

 jroReplica.CreateReplica strReplicaMDB, "JROTest", _ jrRepTypeFull, jrRepVisibilityLocal 

Supply the location of the new replica in the first parameter and a description of this replica in the second parameter. The third parameter indicates which type of replica you want to create and accepts a value from ReplicaTypeEnum. The CreateReplica method allows you to create full and partial replicas. A partial replica uses a filter to determine which data in the parent database it will receive. This parameter is optional and defaults to jrRepTypeFull, which is a full replica. The fourth parameter specifies the value for the Visibility property of the replica you're creating. This property affects how conflicts are handled when changes in the replica that are submitted to the parent database cause conflicts.

The CreateReplica method also accepts optional fifth and sixth parameters to control the priority of the changes in the replica and whether the data in the replica is updatable. By default, the new replica's Priority property will be 90 percent of its parent, and the data in the replica will be updatable.

To control which data a partial replica will receive, use a JRO Filter object. The code that follows creates a partial replica based on a master database, generates a filter for the partial replica, and then calls the Replica object's PopulatePartial method to populate the partial replica:

 'Create the JRO Replica object. Set jroReplica = New JRO.Replica 'Connect to the master database. jroReplica.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strMasterMDB & ";" 'Create a partial replica. jroReplica.CreateReplica strPartialMDB, "JROPartial", _ jrRepTypePartial, jrRepVisibilityGlobal 'Close the connection to the master database. jroReplica.ActiveConnection.Close strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPartialMDB & ";" & _ "Mode=Share Exclusive;" 'Connect to the partial replica. jroReplica.ActiveConnection = strConn 'Specify a filter for the partial replica. jroReplica.Filters.Append "Customers", jrFilterTypeTable, _ "[State]='MA' or [State]='WA'" 'Populate the partial replica. jroReplica.PopulatePartial strMasterMDB 

Now that you understand how to create full and partial replicas and you've read Chapter 7 of the Microsoft Jet Database Engine Programmer's Guide, Second Edition, (nudge, nudge) to determine a replication topology, you need to understand how to synchronize the replicas.

The JRO Replica object has a Synchronize method that you can use to transmit changes from one database to another. The following code connects to the master database and then transmits the changes to a replica database:

 Set jroReplica = New JRO.Replica Set jroReplica.ActiveConnection = strConnMaster jroReplica.Synchronize strPathToReplicaMDB, jrSyncTypeExport, _ jrSyncModeDirect 

The Synchronize method accepts three parameters. The first parameter indicates the location of the database with which you want to synchronize the current replica. In this case, you've connected the master database and you're submitting the location of one of the replica databases in this parameter. The second parameter controls the direction of the synchronization and accepts a value from SyncTypeEnum. Here you're exporting changes, which means changes stored in the master database are transmitted to the replica. You can also specify that changes are only imported (jrSyncTypeImport) or both imported and exported (jrSyncTypeImpExp), which is the default. The third parameter controls the synchronization mode and accepts a value from SyncModeEnum. Jet supports direct replication (jrSyncModeDirect); indirect replication (jrSyncModeIndirect), which is the default; and Internet replication (jrSyncModeInternet). Let's briefly discuss synchronization modes before discussing synchronization directions.

Direct synchronization is the simplest synchronization mode. With this mode, the Jet engine has a direct connection between the two databases being synchronized. With indirect synchronization, as its name implies, no direct connection exists between the databases. Imagine that you want to indirectly export changes from one database to another. You could examine one database's changes, log those changes in a small file, and close the connection to that database. Later, you could connect to the other database and modify its contents based on entries in the log file. This, in a nutshell, is how indirect synchronization works. Internet synchronization is similar to direct synchronization, except two copies of the Jet engine are used over HTTP. Microsoft Office 2000 Developer edition is required for Internet synchronization and includes more documentation on the topic.

When you call the Synchronize method, the second parameter, SyncType, controls the synchronization direction. You can export changes in your current database to the database with which you are synchronizing. You can also import changes, or both import and export changes. If only the master database is updatable, you'll need to transmit changes in one direction only: from the master to the replicas. If you make the replicas updatable, you'll need to transmit changes in both directions when synchronizing.

Two-way synchronization poses challenges similar to using batch optimistic locking in ADO Recordsets. By the time you transmit your updates to the central database, another user might have modified the same data in such a way that your updates fail. Unlike batch optimistic locking in ADO, Jet replication does not provide much information if a conflict occurs. The conflicts are logged in a separate system table. When you call the Synchronize method of the JRO Replica object, you can determine whether conflicts occurred by checking the ConflictTables property on the Replica object.

The ConflictTables property returns an ADO Recordset object. The first Field object in this Recordset contains the name of the table that contained a conflict. The second Field object in this Recordset contains the name of the system table in which that conflict has been stored. If you synchronize two databases and conflicts occur, and then you open either database with Access, Access will inform you that conflicts occurred and the Microsoft Replication Conflict Viewer will prompt you to resolve the conflicts.

The following code shows a simplistic example of how to examine and resolve conflicts programmatically:

 Set jroReplica = New JRO.Replica jroReplica.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strMasterMDB & ";" jroReplica.Synchronize strReplicaMDB, jrSyncTypeImpExp, jrSyncModeDirect If jroReplica.ConflictTables.EOF And jroReplica.ConflictTables.BOF Then MsgBox "No conflicts" Else Set rsConflicts = New ADODB.Recordset Do While Not jroReplica.ConflictTables.EOF 'Build a query to examine the conflicts logged in the system table. strSQL = "SELECT * FROM " & jroReplica.ConflictTables(1) 'Examine the conflicts logged in the master database. rsConflicts.Open strSQL, cnMaster, adOpenKeyset, _ adLockOptimistic, adCmdText Do While Not rsConflicts.EOF 'Resolve the conflict. rsConflicts.Delete rsConflicts.MoveNext Loop rsConflicts.Close 'Examine the conflicts logged in the replica database. rsConflicts.Open strSQL, cnReplica, adOpenKeyset, _ adLockOptimistic, adCmdText Do While Not rsConflicts.EOF 'Resolve the conflict. rsConflicts.Delete rsConflicts.MoveNext Loop rsConflicts.Close Loop Set rsConflicts = Nothing End If 



Programming ADO
Programming MicrosoftВ® ADO.NET 2.0 Core Reference
ISBN: B002ECEFQM
EAN: N/A
Year: 2000
Pages: 131
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net