How to make an operation atomic in VBA so ext query works

[Guest]

I am running a program on Access which uses VBA components to update
information in the database. At times, however, the sequence of operations
would leave the database with information in a state which is not acceptable
to other applications which might query it. The question is how, within this
VBA program, I should bracket sections which should be atomic because they
need to go to completion before an external program should be allowed to
query the database for data. Since the process is ongoing, and multiple
applications are querying the database using ODBC, it is essential that the
atomic operation only causes the queries to wait until it has completed
operation before returning its value but doesn't actually prevent the query
from operating via a lockout. What is the best approach to this issue?

 

[Albert D. Kallal]

You can wrap your update code in a transaction.

These updates will NOT appear in the database run you either:

CommitTrans

or

RollBack

In fact, what ms-access does is cache your updates on your workstation, and
then ONLY when you commitTrans are the updates sent. Any quires executed by
other users during this process will NOT see the results. Thus, your
processing loop, or whatever can take a long time, and during this
time..these updates will NOT be seen by other users/processes.

I suspect once you commitTrans that a table locks do occur..but since all of
the "processing etc" has been done via code..this delay should not be very
long....

Check out

BeginTrans
CommitTrans
Rollback

In the help. Also, remember, the above commands do NOT apply to updates done
via the UI (ie: forms, or editing tables/quires directly etc.).

 

[John Vinson]

The question is how, within this
VBA program, I should bracket sections which should be atomic because they
need to go to completion before an external program should be allowed to
query the database for data.

Open a Transaction, and either Commit it or Rollback if it fails:

Dim ws As Workspace
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim inTrans As Boolean
On Error GoTo Proc_Error
inTrans = False
Set ws = DBEngine.Workspaces(0) ' current workspace
Set db = CurrentDb ' current database
ws.BeginTrans ' begin a transaction
inTrans = True
Set qd = db.Querydefs("mktMakeTableA")
qd.Execute dbFailOnError
Set qd = db.Querydefs("appThisAppendQuery")
qd.Execute dbFailOnError
<etc. etc.>
ws.CommitTrans
inTrans = False

Proc_Exit:
Exit Sub
Proc_Error:
If inTrans Then
ws.Rollback ' undo all the action queries
Msgbox "something appropriate"
Else
Msgbox "a different appropriate message"
End If
Resume Proc_Exit


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps