rewrite your code to the following:
CODE
Private Sub cmd_run_queries_Click()
StartTime "Evaluation Summary Report and the Average Quality Report"
MsgBox "Please wait..... the queries you are running may take several minutes", vbOKOnly, "Running...."
'these queries need to be run for the Evaluation Summary Report and the Average Quality Report
rsql, "qry_DE_evaluation_summary", "for the evaluation summary report"
rsql "qry_MT_evaluation_summary" , "MT_evaluation_summary"
rsql "qry_DE_agents" , "DE_agents"
rsql "qry_agents" , "agents"
rsql "qry_DE_comparison", "DE_comparison"
rsql "qry_MT_comparison", "MT_comparison"
ReportElapsedTime "Query run complete"
End Sub
then
on timing, instead of using
currentdb.execute strSQL ...
1. put this code in a general module
2. use
rSql s, "brief description" then, you can see the timing on each SQL statement (if it executed normally) in the debug window -- you will always see the SQL ...
Don't forget to clear the status bar when your program is done since this sets the status bar with each sql statement... or you can use the "overall timer" posted below, which clears the statusbar
CODE
Sub rSql(pSQL, Optional pMsg, Optional UseDocmd As Boolean)
'written by Crystal
'strive4peace2004@yahoo.ca
On Error GoTo rSQL_Error
Dim mTime As Date
mTime = Now()
If Not IsMissing(pMsg) Then
Debug.Print "---------- " & pMsg
SysCmd acSysCmdSetStatus, pMsg & "..."
End If
Debug.Print pSQL
If Not IsMissing(UseDocmd) Then
'when aggregate functions are used, sometimes it is necessary to use this method
If UseDocmd Then
DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.RunSQL pSQL
DoCmd.Echo True
DoCmd.SetWarnings True
Else
CurrentDb.Execute pSQL
End If
Else
CurrentDb.Execute pSQL
End If
Debug.Print " --- " & Format((Now() - mTime) * 24 * 60 * 60, "#,##0.#") & " seconds" & " --- "
Exit Sub
rSQL_Error:
DoCmd.Echo True
DoCmd.SetWarnings True
End Sub
sometimes I throw a
DoEvents
into the rSQL code when I am having troubles... and sometimes I also set up a table to capture the SQL statements and the times.
and then, here is the overall timer...
CODE
Option Explicit
'written by Crystal
'strive4peace2004@yahoo.ca
Dim gStartTime As Date
'and, if you want to number the lines
'dim gLineNumber As Integer
Sub StartTime(Optional pMsg)
On Error Resume Next
gStartTime = Now()
DoCmd.Hourglass True
' gLineNumber = 0
If IsMissing(pMsg) Then Exit Sub
Debug.Print "--- START-------------" & pMsg & " ----- " & CStr(gStartTime)
End Sub
Sub EndTime()
Debug.Print "--- END-------------" & DateDiff("s", gStartTime, Now()) & " seconds"
DoCmd.Hourglass False
SysCmd acSysCmdClearStatus
End Sub
Sub ReportElapsedTime(Optional pMessage)
On Error Resume Next
Dim m As String, mEndTime As Date
mEndTime = Now()
DoCmd.Hourglass False
'if you are numbering lines... increment counter and change debug.print to include it
'gLineNumber = gLineNumber + 1
If IsMissing(pMessage) Then
m = ""
Else
m = pMessage & ": " & vbCrLf & vbCrLf
Debug.Print "-------------" & pMessage & " ----- "
End If
m = m & "Start Time: " & Format(gStartTime, "hh:nn:ss") & vbCrLf _
& "End Time: " & Format(mEndTime, "hh:nn:ss") & vbCrLf & vbCrLf _
& "Elapsed: " & Format((mEndTime - gStartTime) * 24 * 60, "0.##") & " minutes"
SysCmd acSysCmdClearStatus
MsgBox m, , "Time to execute "
'& CStr(Nz(pFirstSQL, -1))
End Sub
StartTime goes, of course, at the top of the routine
EndTime OR
ReportElapsedTime when it is all over...
When your routine is done, open the DEBUG window
CTRL-G from a module window
and look at what was written -- you will have an overall time as well as a time for each step
If any of your routines use aggregate domain functions (dSum, dCount, etc) -- use the optional 3rd argument of TRUE for "rsql"
ie:
rsql, "queryname", "message", TRUE
