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

[Guest]

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

 

[Guest]

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?

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.

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?

.

 

[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

 

[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.)

 

[ÎÄ×Ð]

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)

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?