Querry run time display

[Octet32]

I am New to VBA i have this querry below which works fine how can i add the
start time and end time and then show how much time it too to run?

thanks

Public Function RunTableToInsert() As Boolean
Dim db As DAO.Database
Dim qdDelete As DAO.QueryDef
Dim qdInsert As DAO.QueryDef
'Dim pStatus As DAO.Parameter

Set db = CurrentDb
Set qdDelete = db.QueryDefs("qdelShipToMaster")
Set qdInsert = db.QueryDefs("qappShipToMaster")
'Set pStatus = qdInsert.Parameters("status")
'pStatus.Value = strStatus

qdDelete.Execute
qdInsert.Execute


End Function

 

[Paolo]

Hi Octet32,

modify your code in this way and the trick is done

Public Function RunTableToInsert() As Boolean
Dim db As DAO.Database
Dim qdDelete As DAO.QueryDef
Dim qdInsert As DAO.QueryDef
'Dim pStatus As DAO.Parameter
Set db = CurrentDb
Set qdDelete = db.QueryDefs("qdelShipToMaster")
Set qdInsert = db.QueryDefs("qappShipToMaster")
'Set pStatus = qdInsert.Parameters("status")
'pStatus.Value = strStatus
start_time=format(now,""hh:mm:ss")

qdDelete.Execute
qdInsert.Execute
end_time=format(now,""hh:mm:ss")
msgbox "Elapsed time " & end_time-start_time

End Function

this is untested so try it.

HTH Paolo

 

[Paolo]

Sorry but I used an extra quote so start_time and end_time must be as follow

start_time=format(now,"hh:mm:ss")
end_time=format(now,"hh:mm:ss")
and is better if you format also the canculation for the elapsed time so
msgbox "Elapsed time " & format(end_time-start_time,"hh:mm:ss")

Cheers Paolo

 

[Octet32]

Thanks Paolo

Hi Octet32,

modify your code in this way and the trick is done

Public Function RunTableToInsert() As Boolean
Dim db As DAO.Database
Dim qdDelete As DAO.QueryDef
Dim qdInsert As DAO.QueryDef
'Dim pStatus As DAO.Parameter
Set db = CurrentDb
Set qdDelete = db.QueryDefs("qdelShipToMaster")
Set qdInsert = db.QueryDefs("qappShipToMaster")
'Set pStatus = qdInsert.Parameters("status")
'pStatus.Value = strStatus
start_time=format(now,""hh:mm:ss")

qdDelete.Execute
qdInsert.Execute
end_time=format(now,""hh:mm:ss")
msgbox "Elapsed time " & end_time-start_time

End Function

this is untested so try it.

HTH Paolo

 

[Daniel Pineault]

Depending on the execution speed you may want to get down to the millisecond
for that you'd need to use the following API

http://allapi.mentalis.org/apilist/timeGetTime.shtml
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.