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

  • Thread starter Thread starter Donald Regener
  • Start date Start date
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
 
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
 
Allen:

I implemented your suggested code.

Everything works great!!!

Appreciate your help.

Thanks,

Don
 
Back
Top