Commit inconsistent?

[lwert]

I have an application where I update values in a table and need to read them
from the table shortly thereafter. To ensure the table is really updated I
have been utilizing the transaction sequence:

Dim ADODBconn as ADODB.Connection
Set ADODBConn = CurrentProject.Connection
ADODBConn.BeginTrans
ADODBConn.Execute strSQL 'For one or more valid SQL Update statements
ADODBConn.CommitTrans
....

And I have also tried:

Dim ADODBconn as ADODB.Connection
Dim ADODBCmd as ADODB.Command
Set ADODBConn = CurrentProject.Connection
Set ADODBCmd = New ADODB.Command
Set ADODBCmd.ActiveConnection = ADODBConn
ADODBCmd.CommandType=adCmdText

ADODBCmd.CommandText = "BeginTransaction"
ADODBCmd.Execute
ADODBCmd.CommandText = strSQL 'For one or more valid SQL Update statements
ADODBCmd.Execute
ADODBCmd.CommandText = "Commit"
ADODBCmd.Execute
....

These sequences seem to run just fine and the data is eventually updated in
the database, but not in the timely manner that I would expect - that is, in
time for the read that follows. As I use this table for a good deal of this
type of activity, I have been careful to perform this type of transaction
processing for every update made, but I see the subsequent read extract the
wrong data most of the time.

Anyone have thoughts? Using XP Pro and MSA2003 with Jet DB.
Thanks

 

[Jamie Collins]

I have an application where I update values in a table and need to read them
from the table shortly thereafter.

You need to refresh the cache e.g. using the ADO JRO library. See:

http://support.microsoft.com/kb/240317/en-us

"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..."

Jamie.

--

 

[lwert]

Thanks Jamie. I figured it was something like that, but I wasn't sure just
what and I didn't have any documentation or books that discussed it.