Update delayed using Jet Engine 4.0

[carlosweiss]

I'm using Access 2000 and Microsoft Jet Engine 4.0 and I'm using these kind of sentences to make updates to the DB:

Dim Aconn As New ADODB.Connection
Aconn.Open "Provider='Microsoft.JET.OLEDB.4.0';Data _  
            Source='c\myDB.mdb'"
Aconn.BeginTrans
strSQL = "update customers set name = 'John' where id = 1"
Aconn.Execute strSQL
Aconn.CommitTrans
Aconn.Close

The problem is that sometimes the updates are not reflected immediately - i.e.: right after the above update, I run a Select statement on the same table and I don't see the change I've just made. I expected that it wouldn't happen if I run the CommitTrans statement.

Thanks!

 

[Golom]

You might try

   Dim je As New JRO.JetEngine
   [COLOR=green]' Refresh the cache to ensure that the latest data
   ' is available.[/color]
   je.RefreshCache Aconn

You will need to reference Microsoft Jet and Replication Objects x.x Library to create the JRO object.

 

[mansii]

Also try adding :

Aconn.Execute strSQL, , adOpenForwardOnly

adOpenForwardOnly option will speed up the transaction.

Regards.

 

[sqit]

Sometimes its all too much for the access database to handle because of syncranisation. I often introduce a short delay in the process to give access to catch up when this happens I use the following:

'Sub to allow a delay to accommodate synchronisation if needed

Public Sub Delay(msecs As Single)
' I use 500 msecs but try experimenting with (say) 200
Dim sngStart As Single
sngStart = Timer
Do While Timer < sngStart + msecs / 1000
DoEvents
Loop
End Sub

Call this from within and near the end of your update sub

 

[dilettante]

Perhaps take a look at some of the Jet Connection Parameters:

Jet OLEDB:Shared Async Delay
(DBPROP_JETOLEDB_SHAREDASYNCDELAY)

Indicates the maximum amount of time, in milliseconds, Jet can delay asynchronous writes to disk when the database is opened in multi-user mode.

Jet OLEDB:Flush Transaction Timeout
(DBPROP_JETOLEDB_FLUSHTRANSACTIONTIMEOUT)

Indicates the amount of time to wait before data stored in a cache for asynchronous writing is actually written to disk. This setting overrides the values for Jet OLEDB:Shared Async Delay and Jet OLEDB:Exclusive Async Delay.

Jet OLEDB:User Commit Sync
(DBPROP_JETOLEDB_USERCOMMITSYNC)

Indicates whether changes made in transactions are written in synchronous or asynchronous mode.

I assume you meant that you are updating with one connection and turning around and querying with another. But if you are using async transactions it might not matter even with the same connection. The defaults for many of these are typically set in:

[tt]HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0[/tt]

ADO Provider Properties and Settings

Can't hurt to take a look. There are lots of cache-control parameters that might make a difference here. Be warned though, you can severely impact performance by effectively disabling Jet's caching mechanisms.

In general: one data source, one connection. Using multiple connections can lead to wasted resources, performance problems, and concurrency mysteries.

 

[tsdragon]

If you use Timer remember that its resolution is often 10-20 msec, so your timings will be very rough. At work I have some code for much higher resolution timers (one has a resolution of about .3 nanoseconds on my computer).

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.