JimP said:
How can I calculate the elapsed time to run a query? e.g.
<something to start a timer>
DoCmd.OpenQuery "SomeQuery"
<something to end a timer when the query displays the recordset>
MsgBox <something to print the elapsed time>
In general you can not time when a query has retrieved all
the records for it's datasheet view. Access runs that kind
of thing asynchronously and retrieves records after the
first screenful on an as needed basis. Opening a form is
done in a similar way so you can only time when something
happened to force all the records to be retrieved (e.g. user
scrolling to last record).
If the activity you want to time takes more than a few
seconds, you can just use the Now function, which has a
resolution of one second.
If you need to time some code that runs in less than a few
seconds, then you need to use something based on an API
call. Here's some code that I've used nearly forever:
Option Compare Database
Option Explicit
Public Declare Function GetClock Lib "Winmm.dll" Alias
"timeGetTime" () As Long
Public Function Timing() As Long
Static lngPrevious As Long
Dim lngNewTime As Long
lngNewTime = GetClock()
Timing = lngNewTime - lngPrevious ' Return interval
since previous call
lngPrevious = lngNewTime
End Function
BUT, because Windows is a multitasking system, there may or
may not be timeslices given to other processes. Code/data
caching and who knows what else going in the computer can
make any measurements you take only be statistically
significant over a large number of executions. Even then
you need to do a careful analysis of the odd looking
measurements (e.g. the first time after restarting the
machine may be the one that is important or one of the
measurements you want to ignore).
