Update Query Question

[DbDezyne]

I have a control button in an Access form that executes an Update Query. Apparently the query takes about 10-15 seconds to execute. I need a way to show that the query is executing such as a "Please Wait" message box while the query is actually executing. I have tried a message box after the DoCmd update query, but the box executes immediately even when the query is still executing. Hope I can get some suggestions here.

thanks

 

[jon98548]

Put your message box before the DoCmd. Just have warn the user that there will be a wait. When they hit the OK button, the DoCmd will execute.

 

[DbDezyne]

Good idea...Is there a way to determine when the query has finished executing so that I can excute something like a "Done" message?

 

[samehkh]

You can create a form and size it like a message box, open it before the query execution and close it after that.

 

[DbDezyne]

That's the problem...how do I determine that the query has finished executing?

 

[jon98548]

What about after the DoCmd you set the focus to the next text box. Then put a message on the GotFocus property of that text box. Might not be the best way, but you would get a message when it started and another when it finished.

Was just reading samehkh's response and that would do it too.

 

[DbDezyne]

I'm not 100% sure about this but, I think the code after the docmd to execute the query will execute while the query is still running. I put a message after the docmd and the message appears immediately even though the query is still executing. I know there must be a solution to this.

 

[samehkh]

If you know that the query takes 10 to 15 seconds you can set the timer property of the suggested popup form to 12,000 ms and then close it on the On_Timer event.

Or if you want the closing of the form to be in better sync with the end of the query. You can replace the update query with a SELECT query and then open the query result as a recordset, loop through it and do the updates in code.
After you close the recordset you then issue the
DoCmd.Close acForm, "FormName"

What do you think?

 

[namliam]

How about "simply"

Docmd.openform (some form saying "Executing query, please wait")
currentdb.execute "Your update query here"
docmd.close "Close your form"

Msgbox "Query done, thank you for waiting", vbokonly

Regards

 

[samehkh]

The problem is that VBA will not wait for the Jet engine to finish executing the query. It will just pass it the command and then go on executing the rest of the code, so the form will close when the query is still running.

 

[namliam]

?? It works just fine on Win2000 Access XP!!!!

Running an update query on a (dummy) table of 1,3 million ! records took allmost 2 mins (timed by access) All that time wait to pop a message.

I used this code:

Private Sub Command1_Click()
Dim s As Date
s = Time()
CurrentDb.Execute "UPDATE Table1 SET Table1.test = 'test'"
MsgBox s & " / " & Time()
End Sub

Regards

 

[samehkh]

Maybe I confused this case with a case when i used to call a DOS batch file from within Access and VBA used to continue Execution before the Batch file was done!!