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

  • Thread starter Sergey Poberezovskiy
  • Start date
S

Sergey Poberezovskiy

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

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?

Sergey Poberezovskiy said:
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?

.
Click to expand...
Click to expand...
 
Top