Delay committing transaction

[Dirk]

Hi,

I am using the following rather mundane code structure:

dbConn.BeginTrans
sSQL = <some update query>
dbConn.Execute sSQL, nAffected, adExecuteNoRecords
sSQL = <some other update query>
dbConn.Execute sSQL, nAffected, adExecuteNoRecords
dbConn.CommitTrans
someListBox.Requery

Now after running this code I noticed that the CommitTrans command returns
control before actually finishing updating. That results in someListbox's
date being incorrect upon Requery. Is there any way to make CommitTrans
execute synchronously so that I do not have to figure out all kinds of tricks
catching events and all?

Thanks in advance.

 

[Graham R Seach]

Dirk,

I don't know of any way to make it run synchronously, however, the following
will loop until it's finished executing.

Do
DoEvents
Loop Until cn.State <> adStateExecuting

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Dirk]

Thanks you for the reply.

However unfortunately this does not work. Apparently the ADO connection
having finished executing does not ensure that the ensuing access commands
(presumably using DAO) get the updated result.

This seems like a rather difficult problem to solve.

 

[TC]

This is outside my area of personal expertise, but: in a multiuser
situation, one user's updates are not /immediately/ visible to other
users, right? They have to wait for a refresh interval? If so, I
suspect that issuing those updates in a transaction, will not make any
difference to that. I suspect that the other users will still have to
wait for the refresh interval, to see the transacted updates.

Remember that a DAO transaction, is nothing remotely like a "real"
transaction in the sense that an ACID-compliant commercial RDBMS would
use that term. In Jet, "transaction" just means, "save all the changes
to memory first, and then, when he says Commit, write them out /ASAP/
so the risk of falling over in the middle of that, is minimized!" So
they are still just updates, written to disk. I imagtine that the
commit would return, before those updates had even been flushed from
the OS cache.

HTH,
TC

 

[TC]

I should have said, "a JET transaction" (not a DAO transaction). The
transactional capability is a feature of Jet, regardless of the access
method.

TC

 

[David C. Holley]

From my limited expereience, that sounds about right.

 

[Dirk]

Well after running into a miriad of problems handling data access using ADO
in Access I have decided to rewrite everything to use DAO:

dbConn.BeginTrans
sSQL = <some update query>
dbConn.Execute(sSQL)
sSQL = <some other update query>
dbConn.Execute(sSQL)
dbConn.CommitTrans
someListBox.Requery

When using ADO I have at one time used a loop between CommitTrans and the
Requery that ran for 1, 2, 3, 4 and 5 seconds doing DoEvents (since
adStateExecuting apparently didn't say anything about the data being stored
or not). Apparently it took about 5 seconds before a Requery on the control
picked up the changes made in the database. That seems way too long.

Anyways none of these things resolve my trust in using ADO in code to work
on Access Jet databases.

Does anyone have good experiences using ADO to do the bulk of the query work
in access?