Running a Series of Update Queries ... Ensure Completion BEFORE Starting Next

D

Donald Regener

I am updating a table using a series of 5 Update
Queries ... Code shown below. I am concerned

with One Query completing BEFORE the Next One Starts. I
am currently ensuring this happens by

leaving the "Action Query" Messages on. My plan is to
supress these Warnings and show the "Hour

Glass" while the event is processing.

Is there any way I can ensure One Query is completed
before starting the next ... DoWhile???

Timer??? ... etc.

Program is Access 2000

Appreciate any help that can be provided.


Code (This works using Action Query "Warning Messages". I
have not tried supressing Warning

Message and trying. I am concerned that One Query is
completed BEFORE the Next One is Started.)

'Update Booth Number & Size
Dim stDocName As String
stDocName = "2 Contract_ExpoCAD_Exh_Update_1_Booth"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'Update Sands2 Venue
stDocName = "2 Contract_ExpoCAD_Exh_Update_2_Sands2"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'Update Sands1 Venue
stDocName = "2 Contract_ExpoCAD_Exh_Update_3_Sands1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'Update MB1 Venue
stDocName = "2 Contract_ExpoCAD_Exh_Update_4_MB1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'Update MB1 Venue
stDocName = "2 Contract_ExpoCAD_Exh_Update_5_MB2"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Thanks,

Don
 
A

Allen Browne

For an action query, RunSql might give better results than OpenQuery.

However, using the Execute method would be better still:
Dim db As DAO.Database
Set db = dbEngine(0)(0)
db.Execute "SomeQuery", dbFailOnError
db.Execute "AnotherQuery", dbFailOnError

This does not bother the user with confirmation dialogs, but if one query
fails your error-handler takes over and prevents the rest from running.

If you really want an all-or-nothing result, you can wrap the entire
operation in a transaction. Details and example:
http://allenbrowne.com/ser-37.html
 
D

Donald Regener

Allen:

I implemented your suggested code.

Everything works great!!!

Appreciate your help.

Thanks,

Don
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top