Querry run time display

  • Thread starter Thread starter Octet32
  • Start date Start date
O

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
 
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
 
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
 
Thanks Paolo

Paolo said:
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
Click to expand...
 
Back
Top