Why is my Access database not updating when I read it from another process?

Question

In my application, I am doing the following things:

• Open Access Database (.mdb) with Jet / ADO and VB6
• Clear and refill the table with new data
• Close database
• Run another process that does something with the new data.

The problem is that sometimes the second process cannot find new data. Sometimes the table is just empty, sometimes RecordCount> 0, but EOF is true, and I cannot do MoveFirst or MoveNext. In a nutshell: all kinds of strange things.

My current workaround is to add a delay between closing the database and starting the second process.

• What's going on here?
• Can i do something? (In addition to using a different database)

Answer 1

Just a guess, but I may be due to the fact that the Jet engine has a read cache and lazy writes:

How to implement multi-user custom counters in Jet 4.0 and ADO 2.1

"Microsoft Jet has a read cache that is updated every millisecond of PageTimeout (default 5000 ms = 5 seconds). It also has a lazy write mechanism that runs in a separate thread for main processing and thus writes changes to disk asynchronously. These two mechanisms "can help improve performance, but in certain situations that require high concurrency, they can cause problems."

Jet RefreshCache Jet OLEDB: Transaction Commit Mode 1 ( ADO DAO Jet , ).

P.S. Access (.mdb), "Jet" "Jet", SO, :)

Answer 2

, .

. , .

• , ADO Connection.BeginTrans, .
• ( ADO Connection.CommitTrans).
• JRO.JetEngine.RefreshCache, .

, JRO.JetEngine Microsoft Jet Replication Objects 2.1 VB.

    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

Answer 3

Since the first process is the only process with open MDB, it can be a little lazy in writing material back to a file. Even after you finish the process, there may be a delay, while the OS will record outstanding pages, and this may happen after the process signals the completion of the process.

My recommendation would be to stop using Access, instead use SQL Server 2008 Express.