Jurgen Welz
jwelz at hotmail.com
Mon Jan 12 22:48:26 CST 2015
The sample code I pasted had the format messed up as the line breaks disappeared. I'll try again:
Swap the line in the SpeedTest sub below
Set db = fnDb
for
Set db = CurrentDb
You could also replace it with
Set db = dbengine(0)(0)
and see if there is any difference.
'Code below to paste in a Module with the Declare line above any procedures:
Public Declare Function timeGetTime Lib "Winmm" () As Long
'Speed test procedure. lngx is the number of iterations to run
Public Sub SpeedTest()
Dim lngx As Long
Dim lngi As Long
Dim db As DAO.Database
lngi = timeGetTime
For lngx = 0 To 999
Set db = fnDB
Set db = Nothing
Next
Debug.Print timeGetTime - lngi
End Sub
'Function that sets a database object variable to a previously instantiated static variable
Public Function fnDB() As DAO.Database
On Error GoTo ErrorHandler
Static db As DAO.Database
Dim lngi As Long
lngi = Len(db.Name)
ExitRoutine:
On Error Resume Next
Set fnDB = db
Exit Function
ErrorHandler:
With Err
Set db = CurrentDb
End With
'Resume 0
Resume ExitRoutine
End Function
'End of code to paste into module
You can easily test the difference in speed with your own database and that will vary with complexity and the horse power you've got. You can change the number of iterations by changing the loop counter end number. The immediate window will display then total number of milliseconds to create and destroy a database variable that number of times. The code as written creates and destroys the database variable 1000 times. In my environment, CurrentDb called 1000 times takes 2 seconds, or 2 thousandths of a second each time it's called. If I'm not calling CurrentDb in a loop, I don't think anyone will notice the 2 milliseconds. Using the static variable in the function takes 4 millionths of a second. 500 times faster sounds impressive, but there are not many routines so complex that using one over the other would make a noticeable difference. I leave it to anyone else to set the variable to dbengine(0)(0) and report on any difference.
Ciao
Jürgen Welz
Edmonton, Alberta