Waiting for SQL commands to complete in VB - Mission Impossible?

[LarryInConfusion]

I'm trying to execute an SQL UPDATE query followed by a form repaint in
Access VB triggered by a form button. The problem is, the query apparently
executes asynchronously with the VB code, so the repaint happens before the
UPDATE is complete. I'm sure the SQL is executing eventually, and the repaint
works also. If I click the button TWICE, the screen repaints with the
correctly updated info.

Here's my code:

Private Sub Command63_Click()
On Error GoTo Err_Command63_Click

Dim SQL As String
SQL = "UPDATE Risk SET Risk.RiskBudgetValue = Risk.RiskProbability *
Risk.RiskImpactCost *0.01;"

' UseTransaction (second parameter of the DoCmd.RunSQL command) set to
True (-1)
DoCmd.RunSQL SQL, -1

' The following command executes before the query completes
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command63_Click:
Exit Sub

Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click

End Sub

There is an implication in the RunSQL documentation that setting
UseTransaction to True (–1) [as I have done in my code] executes the query
in a transaction, and should have the desired effect. However, this doesn't
appear to be correct, based on my very frustrating experience.

Anybody have a secret method for waiting until the RunSQL query completes
before executing the next command?

[Sergey Poberezovskiy]

You could use the following:
CurrentDb.Execute SQL, dbFailOnError
instead of using DoCmd.RunSQL
this will ensure sync processing
HTH

-----Original Message-----
I'm trying to execute an SQL UPDATE query followed by a

form repaint in

Access VB triggered by a form button. The problem is, the

query apparently

executes asynchronously with the VB code, so the repaint

happens before the

UPDATE is complete. I'm sure the SQL is executing

eventually, and the repaint

works also. If I click the button TWICE, the screen

repaints with the

correctly updated info.
Private Sub Command63_Click()
On Error GoTo Err_Command63_Click
Dim SQL As String
SQL = "UPDATE Risk SET Risk.RiskBudgetValue =

Risk.RiskProbability *

Risk.RiskImpactCost *0.01;"
' UseTransaction (second parameter of the

DoCmd.RunSQL command) set to

True (-1)
DoCmd.RunSQL SQL, -1
' The following command executes before the query

completes

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,

acMenuVer70

Exit Sub
MsgBox Err.Description
Resume Exit_Command63_Click
End Sub
There is an implication in the RunSQL documentation that

setting

UseTransaction to True (â?"1) [as I have done in my

code] executes the query

in a transaction, and should have the desired effect.

However, this doesn't

appear to be correct, based on my very frustrating

experience.

Anybody have a secret method for waiting until the RunSQL

query completes

before executing the next command?
.

[LarryInConfusion]

Sergey,

Thanks for taking the time to respond. However, your suggestion doesn't fix
the problem. I still get the same behavior. Maybe we're dealing with a new
"feature" of Access 2003....?

Anyone else have a similar experience?

Post by Sergey Poberezovskiy
CurrentDb.Execute SQL, dbFailOnError
instead of using DoCmd.RunSQL
this will ensure sync processing
HTH

-----Original Message-----
I'm trying to execute an SQL UPDATE query followed by a

form repaint in

Access VB triggered by a form button. The problem is, the

query apparently

executes asynchronously with the VB code, so the repaint

happens before the

UPDATE is complete. I'm sure the SQL is executing

eventually, and the repaint

works also. If I click the button TWICE, the screen

repaints with the

correctly updated info.
Private Sub Command63_Click()
On Error GoTo Err_Command63_Click
Dim SQL As String
SQL = "UPDATE Risk SET Risk.RiskBudgetValue =

Risk.RiskProbability *

Risk.RiskImpactCost *0.01;"
' UseTransaction (second parameter of the

DoCmd.RunSQL command) set to

True (-1)
DoCmd.RunSQL SQL, -1
' The following command executes before the query

completes

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,

acMenuVer70

Exit Sub
MsgBox Err.Description
Resume Exit_Command63_Click
End Sub
There is an implication in the RunSQL documentation that

setting

UseTransaction to True (â?"1) [as I have done in my

code] executes the query

in a transaction, and should have the desired effect.

However, this doesn't

appear to be correct, based on my very frustrating

experience.

Anybody have a secret method for waiting until the RunSQL

query completes

before executing the next command?
.

[Malcolm Cook]

I don't think transactions have anything to do with your problem. The
update will be an implicit transaction.

What is this line supposed to do? :

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Try instead the combination:
CurrentDb.Execute SQL, dbFailOnError
me.requery
--
Malcolm Cook - ***@stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

Post by LarryInConfusion
I'm trying to execute an SQL UPDATE query followed by a form repaint in
Access VB triggered by a form button. The problem is, the query apparently
executes asynchronously with the VB code, so the repaint happens before the
UPDATE is complete. I'm sure the SQL is executing eventually, and the repaint
works also. If I click the button TWICE, the screen repaints with the
correctly updated info.
Private Sub Command63_Click()
On Error GoTo Err_Command63_Click
Dim SQL As String
SQL = "UPDATE Risk SET Risk.RiskBudgetValue = Risk.RiskProbability *
Risk.RiskImpactCost *0.01;"
' UseTransaction (second parameter of the DoCmd.RunSQL command) set to
True (-1)
DoCmd.RunSQL SQL, -1
' The following command executes before the query completes
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit Sub
MsgBox Err.Description
Resume Exit_Command63_Click
End Sub
There is an implication in the RunSQL documentation that setting
UseTransaction to True (-1) [as I have done in my code] executes the

query

Post by LarryInConfusion
in a transaction, and should have the desired effect. However, this doesn't
appear to be correct, based on my very frustrating experience.
Anybody have a secret method for waiting until the RunSQL query completes
before executing the next command?

[Van T. Dinh]

My guess is that without transaction, your SQL will run faster. Also, I
would try the dbRefreshCache to make sure the update is actually updated
into the Table rather than still in cache.

Try:

DoCmd.RunSQL SQL, False
DBEngine.Idle dbRefreshCache
...

Also, you mentioned "repaint" but I wonder whether you actually need Requery
rather than Repaint (I don't know what your DoMenuItem is supposed to do as
this is based on Menu for A95 which I no longer use.)
--
HTH
Van T. Dinh
MVP (Access)

Post by LarryInConfusion
I'm trying to execute an SQL UPDATE query followed by a form repaint in
Access VB triggered by a form button. The problem is, the query apparently
executes asynchronously with the VB code, so the repaint happens before the
UPDATE is complete. I'm sure the SQL is executing eventually, and the repaint
works also. If I click the button TWICE, the screen repaints with the
correctly updated info.
Private Sub Command63_Click()
On Error GoTo Err_Command63_Click
Dim SQL As String
SQL = "UPDATE Risk SET Risk.RiskBudgetValue = Risk.RiskProbability *
Risk.RiskImpactCost *0.01;"
' UseTransaction (second parameter of the DoCmd.RunSQL command) set to
True (-1)
DoCmd.RunSQL SQL, -1
' The following command executes before the query completes
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit Sub
MsgBox Err.Description
Resume Exit_Command63_Click
End Sub
There is an implication in the RunSQL documentation that setting
UseTransaction to True (-1) [as I have done in my code] executes the

query

Post by LarryInConfusion
in a transaction, and should have the desired effect. However, this doesn't
appear to be correct, based on my very frustrating experience.
Anybody have a secret method for waiting until the RunSQL query completes
before executing the next command?

[ÎÄ×Ð]

Post by Van T. Dinh
My guess is that without transaction, your SQL will run faster. Also, I
would try the dbRefreshCache to make sure the update is actually updated
into the Table rather than still in cache.
DoCmd.RunSQL SQL, False
DBEngine.Idle dbRefreshCache
...
Also, you mentioned "repaint" but I wonder whether you actually need Requery
rather than Repaint (I don't know what your DoMenuItem is supposed to do as
this is based on Menu for A95 which I no longer use.)
--
HTH
Van T. Dinh
MVP (Access)

Post by LarryInConfusion
I'm trying to execute an SQL UPDATE query followed by a form repaint in
Access VB triggered by a form button. The problem is, the query apparently
executes asynchronously with the VB code, so the repaint happens before

the

Post by LarryInConfusion
UPDATE is complete. I'm sure the SQL is executing eventually, and the

repaint

Post by LarryInConfusion
works also. If I click the button TWICE, the screen repaints with the
correctly updated info.
Private Sub Command63_Click()
On Error GoTo Err_Command63_Click
Dim SQL As String
SQL = "UPDATE Risk SET Risk.RiskBudgetValue =

Risk.RiskProbability

Post by Van T. Dinh
*

Post by LarryInConfusion
Risk.RiskImpactCost *0.01;"
' UseTransaction (second parameter of the DoCmd.RunSQL command) set

to

Post by LarryInConfusion
True (-1)
DoCmd.RunSQL SQL, -1
' The following command executes before the query completes
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit Sub
MsgBox Err.Description
Resume Exit_Command63_Click
End Sub
There is an implication in the RunSQL documentation that setting
UseTransaction to True (-1) [as I have done in my code] executes the

query

Post by LarryInConfusion
in a transaction, and should have the desired effect. However, this

doesn't

Post by LarryInConfusion
appear to be correct, based on my very frustrating experience.
Anybody have a secret method for waiting until the RunSQL query completes
before executing the next command?