We help IT Professionals succeed at work.

DoCmd.OpenQuery MyQueryName | How do I know it's done?

genehead
genehead asked
on
The code below loops through a table of query names.  It displays the query name in the text box Me.txtID and then executes the query.  Finally it moves to the next record and loops.  It works as predicted so long as the message box is present.  However, when I comment out the message box, all the queries are run but the me.txtID box is only updated on every 4-5 records.  Every query requires at least 5 seconds to execute when launched from Access Database Window.  

QUESTION:  In this procedural code, when the DoCmd.OpenQuery is executed, is the next line of code executed immediately or after the launched query completes?  If immediately then I could have a problem.  

If query #2 is dependent on the results of query #1 and query #1 requires 30 seconds to complete execution, then query #2 will probably begin before query #1 is complete.

Is there a DoCmd.? property I can test to see if the command is complete?

Any insight is appreciated -

<`{{{><
Gene


Do While Not rs.EOF()

    ' Display the name of the query
    Me.txtID.Value = rs("CreateTableQueryName")

    '  Next run the Create Table Query
    If IsNull(rs("CreateTableQueryName")) Then
        '  skip it
    Else
        DoCmd.OpenQuery (rs("CreateTableQueryName"))
    End If

    rs.MoveNext

  MsgBox "Testing"

Loop
Comment
Watch Question

try adding a
   DoEvents()
and/or
   Me.Repaint
after the line
   DoCmd.OpenQuery (rs("CreateTableQueryName"))


It sounds like it's just not refreshing the value of the textbox due to the processor-intensive nature of the procedure.  I'm %99 sure that the queries in this code would be run subsequently.  I'm pretty sure you have to set the dbAsynchronous flag on a QueryDef.Execute or ODBC Direct connection in order for the queries to be run without waiting for the previous one to finish.
I agree, that should do it.

It may help the process to add a delay between running each query with the timer to wait a few seconds before starting your loop again.


Dim PauseTime, Start, Finish, TotalTime
    PauseTime = 5    ' Duration of 5 seconds
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes.
    Loop
   

Author

Commented:
Seems simple now that I think about it, but it wasn't simple before I saw your answer.  Thanks

<`{{><
g
glad I could help!  :-)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.