Macro that shows Start and Stop times of queries 1

[TJVFree]

I know there is away to in a macro show the start and stop times of the actions/queries that run in a macro.

Could someone please remind me how this is down again?

Thanks for your help
TCB

 

[kjv1611]

The only way I can think of would be something like this (and I'm assuming you mean in VBA code, not a macro... so if it's in a macro setting somewhere, I've no idea, b/c never tried). But if I were doing it, I'd probably go with this.. should at least be close enough:

Private Sub TestQueryRun()
  Dim db as DAO.Database
  Dim rs as DAO.Recordset
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblMyHistoryTable")
  
  rs.AddNew
  rs.Fields("StartDate") = Now()
  rs.Update

  DoCmd.OpenQuery "qryMyQuery"

  rs.Edit
  rs.Fields("EndDate") = Now()
  rs.Update

  rs.Close
  Set rs = Nothing
  db.Close
  Set db = Nothing

End Sub

This is untested, and frankly could be done other ways - you could use SQL statements to add/update history records as well.. and I'm just guessing at how you might have things set up... I would have a HistID that would be an AutoNumber, so it gets set automatically as well...

 

[madelca100]

this is what I use to print in the Immediate Window:

debug.print "Starting process xyz at : " now()
code
code
code
debug.print "Ending process xyz at : " now()

(note: VBEditor will insert a semicolon.)

 

[TheAceMan1]

How are ya CoreyVl . . .

Times returned by the [blue]Now[/blue] have a resolution of 1 second. Considering the speed of processors these day ...
[blue]1GHz processor = 1 nano-second/cycle
2GHz processor = 0.5 nano-seconds/cycle
3GHz processor = 0.33 nano-seconds/cycle[/blue]
... if what your checking executes in less than 1 second, you'll always return zero (the difference is reading [blue]Now[/blue] before & after your code executes).

So you need something with more resolution. There's an API function [blue]timeGetTime[/blue] that reads the system time in milliseconds. At least its [blue]1000 times more accurate[/blue] than the Now() function.

To install the API, in the [blue]declaration section[/blue] of a module in the modules window, copy/paste the following line:

[blue]Public Declare Function timeGetTime Lib "winmm.dll" () As Long[/blue]

Thats it! ... Call the function from anywhere you like. The following is the secnario I used to test the time of 3 action queries:

[blue]   Dim db As DAO.Database, StartTime As Long, EndTime As Long
   
   Set db = CurrentDb
   
   StartTime = [purple][b]timeGetTime[/b][/purple]
   
   db.Execute "DELETE tblLottoNumbers.* " & _
               "FROM tblLottoNumbers;", dbFailOnError
   
   db.Execute "INSERT INTO tblLottoNumbers (Num) " & _
              "SELECT qryDayN.Dy " & _
              "FROM qryDayN;", dbFailOnError
   
   db.Execute "INSERT INTO tblLottoNumbers (Num) " & _
              "SELECT qryYearN.Expr1 " & _
              "FROM qryYearN;", dbFailOnError
              
   EndTime = [purple][b]timeGetTime[/b][/purple]

Debug.Print EndTime - StartTime & " milliseconds"[/blue]

And the immediate window reveals [blue]24 milliseconds[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kjv1611]

Thanks for that reference, TheAceMan1. That may very well come in handy for me as well some time in the near future.