In my application I do the follwing things:
The problem is that sometimes the second process cannot find the new data. Sometimes the table is just empty, sometimes RecordCount > 0, but EOF is true and I cannot do a MoveFirst or MoveNext. In a nutshell: All kinds of weird things.
My current workaround is adding a delay between closing the database and starting the second process.
Reasons why a Query or Recordset is not UpdateableThe field is a calculated field, so it can't be edited. You don't have permissions/rights to edit the table or database. The query uses VBA functions or user defined functions and the database isn't enabled (trusted) to allow code to run.
Just a guess but I might be due to the fact that the Jet engine features a read cache and lazy writes:
How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1
"Microsoft Jet has a read-cache that is updated every PageTimeout milliseconds (default is 5000ms = 5 seconds). It also has a lazy-write mechanism that operates on a separate thread to main processing and thus writes changes to disk asynchronously. These two mechanisms help boost performance, but in certain situations that require high concurrency, they may create problems."
The article suggests using Jet's RefreshCache method and to set the Jet OLEDB:Transaction Commit Mode to 1 millisecond (one advantage for ADO over DAO for Jet is that you can alter this setting without changing the value in the registry).
P.S. you should consider editing Access database (.mdb) to mention 'Jet' instead and using the 'Jet' tag too, otherwise you'll get a comment from a certain SO user who is pernickety about these things :)
This Microsoft Knowledge Base article explains how to do it.
Here's an excerpt with sample code. The code uses two connections from one process, so you need to pull the reading part into your second process.
Note that JRO.JetEngine is included by adding a reference to the Microsoft Jet And Replication Objects 2.1 Library to your VB project.
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 love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With