HOWTO: Synchronize Writes and Reads with the Jet OLE DB Provider and ADO

ID: Q200300

The information in this article applies to:

Microsoft OLE DB Provider for Jet, version 4.0

SUMMARY

Every ADO connection that uses the Jet OLE DB Provider maintains an independent Jet session. This results in a delay between writes on one connection being read on a second connection, even if you have two connections in the same database application.

This asynchronous write/read behavior is by design. Microsoft Jet uses a page buffering system to provide enhanced database performance and this page buffering system cannot be turned off.

To demonstrate Jet's delayed write/read behavior when using independent ADO connections, run the following code from a button in a simple VB form:


Const USE_SAME_CONNECTION = False

Sub MissedReadsDemo()
Dim conn1 As New ADODB.Connection
Dim conn2 As New ADODB.Connection
Dim rs As New ADODB.recordset
Dim strConnect As String
Dim i As Long

    ' Set up our connection string (requires a database named c:\db1.mdb).
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb"

    ' Open connection 1 and drop and re-create test table.
    conn1.CursorLocation = adUseServer
    conn1.Open strConnect
    On Error Resume Next
    conn1.Execute "drop table tmpTest", , _
        adExecuteNoRecords + adCmdText
    On Error GoTo 0
    conn1.Execute "create table tmpTest (id long)", , _
        adExecuteNoRecords + adCmdText
    
    ' Close connection 1 to flush the creation of table tmpTest. 
    conn1.Close
    
    ' Now open connection 1 and connection 2.
    conn1.Open strConnect
    conn2.Open strConnect
    
    ' Insert 10 records using connection 1.
    For i = 1 To 10
        conn1.Execute "insert into tmpTest (id) values (1)", , _
            adExecuteNoRecords + adCmdText
    Next i
    
    ' Attempt to read records using second connection if
    ' USE_SAME_CONNECTION is set to False.
    If (USE_SAME_CONNECTION) Then
        Set rs = conn1.Execute("select * from tmpTest", , adCmdText)
    Else
        Set rs = conn2.Execute("select * from tmpTest", , adCmdText)
    End If
    
    ' Count records in our table (should be 10).
    i = 0
    While Not rs.EOF
        i = i + 1
        rs.MoveNext
    Wend
    rs.Close

    If (USE_SAME_CONNECTION) Then
        MsgBox "Read " & i & " records using same connection."
    Else
        MsgBox "Read " & i & " records using 2 different connections."
    End If
    
    conn1.Close
    conn2.Close

End Sub

Note that if you run the above sample code over and over, you will intermittently get 10 records returned or 0 records returned when the USE_SAME_CONNECTION flag is set to False. This demonstrates that writes and reads when using two ADO connections with the Jet OLEDB Provider 4.0 are not completely synchronous. If you set the USE_SAME_CONNECTION flag to True, you will always get 10 records returned. This demonstrates that writes and reads on the same ADO connection are 100% synchronous when using the Jet OLEDB Provider 4.0.

MORE INFORMATION

In certain situations you may need to use two separate ADO connections when writing and reading data from an Access database using the Jet OLEDB Provider. For example, if you have two separate processes that are writing and reading to the same Access database, there is no way to share a single connection. In this situation, you can synchronize writes and reads with separate ADO connections if you follow these guidelines:

The writer must start a transaction, using ADO's Connection.BeginTrans, prior to writing the data.

The writer must make the database updates and then commit the transaction (using ADO's Connection.CommitTrans).

The reader must call JRO.JetEngine.RefreshCache passing in it's connection prior to attempting to read the data.

Note that JRO.JetEngine is included by adding a reference to the Microsoft Jet And Replication Objects 2.1 Library to your VB project.

The following code example demonstrates how to synchronize two connections using the above method:


Sub SyncReadDemo()
Dim conn1 As New ADODB.Connection
Dim conn2 As New ADODB.Connection
Dim rs As New ADODB.recordset
Dim JRO As New JRO.JetEngine
Dim strConnect As String
Dim i As Long

    ' Set up our connection string (requires a database named c:\db1.mdb).
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb"

    ' Open connection 1 and drop and re-create test table.
    conn1.CursorLocation = adUseServer
    conn1.Open strConnect
    On Error Resume Next
    conn1.Execute "drop table tmpTest", , _
        adExecuteNoRecords + adCmdText
    On Error GoTo 0
    conn1.Execute "create table tmpTest (id long)", , _
        adExecuteNoRecords + adCmdText
    
    ' Close connection 1 to flush the creation of table tmpTest. 
    conn1.Close
    
    ' Now open connection 1 and connection 2.
    conn1.Open strConnect
    conn2.Open strConnect
    
    ' Insert 10 records using connection 1.
    ' Note we must perform all writes inside of a transaction.
    conn1.BeginTrans
    For i = 1 To 10
        conn1.Execute "insert into tmpTest (id) values (1)", , _
            adExecuteNoRecords + adCmdText
    Next i
    conn1.CommitTrans
    
    ' Refresh cache for reader connection.
    JRO.RefreshCache conn2
    Set rs = conn2.Execute("select * from tmpTest", , adCmdText)
    
    ' Count records in our table (should be 10).
    i = 0
    While Not rs.EOF
        i = i + 1
        rs.MoveNext
    Wend
    rs.Close

    MsgBox "Read " & i & " records using different connections."
    
    conn1.Close
    conn2.Close

End Sub

If you run the above code, you should always get 10 records returned by the reader connection. If you comment out the call to RefreshCache, or do not use the transaction when writing the data, the number of records returned will not always be 10. Following this method will allow synchronized writes and reads between separate processes as well when using ADO with the Microsoft Jet OLE DB Provider 4.0.

Note that this method does not work when using the Microsoft Access ODBC driver with ADO. The Microsoft Access ODBC driver does not allow synchronized writes and reads between two continuously open ADO connections under any circumstance. In order to successfully write and read data using the Microsoft Access ODBC driver with ADO, the writer connection must be closed after the write is complete and the reader connection must be closed and re-opened prior to reading the data. Use the following method to synchronize writes and reads between 2 connections with the Microsoft Access ODBC driver and ADO:

The writer must make the database updates (with or without a transaction) and then close it's connection.

The reader must close and re-open it's connection prior to attempting to read the data.

REFERENCES

Q180223 HOWTO: Synchronizing Reads and Writes Between Two DAO Processes

Additional query words: kbDSupport

Keywords : kbADO kbDatabase kbJET kbOLEDB kbProvider kbGrpVCDB kbGrpMDAC
Version : WINDOWS:4.0
Platform : WINDOWS
Issue type : kbhowto