Query duration time

  • Thread starter Thread starter SJW_OST
  • Start date Start date
S

SJW_OST

Hi,

I have code that is accessing a very large database via ODBC connection. It
is slow due to the size of the database being accessed and works fine but I
want to know how long it takes my query to gather the data. How would I write
code to time the duration it takes for the query to run and then display the
time duration in a message box?

Any help is greatly appreciated.
Stephen
 
SJW_OST said:
I have code that is accessing a very large database via ODBC connection. It
is slow due to the size of the database being accessed and works fine but I
want to know how long it takes my query to gather the data. How would I write
code to time the duration it takes for the query to run and then display the
time duration in a message box?
Click to expand...


That's fine if you want to know the time to get the first
record.

OTOH, if you expect to measure the retrieval time for the
entire recordset, then add a the line:
rs.MoveLast
after the Set rs line. The MoveLast forces Access to
retrieve all the records immediately.

OTOOH, it might(?) not matter because Access usually runs
the part of the query beyond what is needed immediately (the
first record in a recordset, the first screen's worth of
records for a query's datasheet or ...) as an asynchronous
task. And measuring the time for an asynchronous task is
nearly meaningless.
 
I tried using this code in every way I know how and could not get it to work.
I kept getting Halt and RunTime errors. I thank you for the suggestion and I
appologize because I asked my question on the wrong operator. I meant to ask
how to time a macro from start to finish. I have made a new posting for this
question.

Again I appologize but I thank you very much for the help!.
 
Stephen:

Copy all the code below to a new module.
It contains some functions and subprocedures you can
call before and after running your query.
I'm assuming you can run your query from code and make
the appropriate calls to the following module before
and after running your query.


Option Explicit

' The timeGetTime function returns the number of milliseconds
' that have elapsed since since the current Windows session
' started. The timeGetTime function is fast, accurate and
' doesn't "roll over". Therefore, the function is suitable
' for measuring elapsed times. The timeGetTime function
' is in the Windows system file "winmm.dll", hence the
' following declaration, which allows VBA to access the
' function:
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Private mlngStartTime As Long

Public Function GetElapsedTime(fStart As Boolean) As Long

' This function can be called by another procedure to get
' the time when that procedure started and stopped,
' thus providing the elapsed time, ie the time it took
' for the other procedure to run.

Static lngStartMillisecond As Long

If fStart Then
lngStartMillisecond = timeGetTime()
Else
GetElapsedTime = timeGetTime() - lngStartMillisecond
End If

End Function


' ALTERNATIVE METHOD:

Public Sub StartTimer()
mlngStartTime = 0
' Start timer storing value in global lngStartTime:
mlngStartTime = timeGetTime()
End Sub

Public Function StopTimer() As Long
' Stop timer and return elapsed time in milliseconds:
StopTimer = timeGetTime() - mlngStartTime
End Function


' WAIT:

Public Sub WaitAWhile(lngMillisecondsToWait As Long)

Dim lngStart As Long
Dim lngEnd As Long

lngStart = timeGetTime()
lngEnd = lngStart

Do Until Abs(lngEnd - lngStart) > Abs(lngMillisecondsToWait)
DoEvents
lngEnd = timeGetTime()
Loop

End Sub



Regards
Geoff





message
news:[email protected]...
 
Back
Top