Timing of Access Queries

[RogerC]

I find some lengthy queries on large tables take so long that I end up leaving the PC on overnight. Does anyone know a way of timing how long Access actually takes to run a query? (Other than stopwatches etc

 

[RogerC]

Well the Performance Analyzer seems to be happy with the query, I just think it's the size of the tables involved. (1.89m records in one of the tables.)
I must confess to not using forms much. I'm aware that they have time features but have never ran a query from one. Is it a straightforward process to run a qery from a form and stamp the start and finish time someware?

 

[David R]

Pat, or anyone who's done this:

Do you remember how you accomplished this with queries? I always get a result of 0.00E+00, presumably because it is writing the second time immediately after starting the query, not when it finishes.

It made no difference that my code was behind the unbound form itself, and not in a separate module. Here's what I attempted:

Private Sub Command4_Click()
Dim st As Single, nd As Single

    Dim stDocName As String

    stDocName = "query1"
    st = Now()
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    nd = Now()
    Me.Text0 = nd - st
End Sub

I just tried something I found in another post, but it gave identical results as well:

Dim stDocName As String

    stDocName = "query1"
    Debug.Print "Start: " & Now()
    DoCmd.OpenQuery stDocName
    Debug.Print "End: " & Now()

Is this because the query is simply running too fast? I can't fathom that it is; there are over a million dummy records in this table, and while it takes Access no time to calculate the result, the "Record Count" does take a while to show up at the bottom. Rushmore query strategy?

I guess what I need is a way to stop the second Now() statement from running until the Query has finished.