from David W Fenton's answer to the SU question Does MS Access 2003 contain a general purpose SQL console
The problem with using CurrentDB as your execution object is that it returns a new database object each time you call it
My question is: Is there an overhead when using CurrentDb to execute SQL or open a recordset, and should I avoid it?
It's not clear what you mean by the term "overhead," so I don't know how anyone could answer your question as worded.
But the subject of DBEngine(0)(0) vs. CurrentDB has been discussed quite extensively over the years in the Access newsgroups. I long ago made my peace with using CurrentDB, so I'll summarize the situation as I see it.
Dim db As DAO.Database
Dim i As Integer
Debug.Print "Start CurrentDB: " & Now()
For i = 1 to 1000
Set db = CurrentDB
Set db = Nothing
Next i
Debug.Print "End CurrentDB: " & Now()
Debug.Print "Start DBEngine(0)(0): " & Now()
For i = 1 to 1000
Set db = DBEngine(0)(0)
Set db = Nothing
Next i
Debug.Print "End DBEngine(0)(0): " & Now()
If I recall correctly, the ADH97 said that DBEngine(0)(0) was something like 17 times faster.
But look at that code -- it doesn't test anything that is useful. Remember, both CurrentDB and DBEngine(0)(0) return pointers to the database currently open in the Access UI (with certain caveats, below, for DBEngine(0)(0)).There is no place in an Access app where either of those loops is going to be useful in any way. In real code, you do this:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("a SQL SELECT")
[do something with the recordset]
rs.Close
Set rs = db.OpenRecordset("another SQL SELECT")
[do something with this other recordset]
rs.Close
Set rs = Nothing
db.Execute("A SQL DML statement")
Debug.Print db.RecordsAffected
Set db = Nothing
While DBEngine(0)(0) may be 1700% faster in a loop, IT DOESN'T MATTER, because you're never going to repeatedly return a reference to the database currently open in the Access UI enough times for the difference to be anything but completely negligible (we're talking milliseconds here, though, of course, CurrentDB will take longer for databases with more objects).
So, first off, before I explain why there's a difference, you must first recognize that the performance difference is completely immaterial, as the only circumstances in which it can exceed the most trivial difference is a circumstance that would be brain-dead stupid code.
Now, why the difference?
Well, there are two main reasons:
DBEngine(0)(0) returns the collections as they were initialized when the database currently open in the user interface was first open, unless you manually refresh the collections. So, if you add a new saved QueryDef, for it to be available in code using DBEngine(0)(0), after adding the new QueryDef you have to call
DBEngine(0)(0).QueryDefs.RefreshBefore that, your new query won't be in the QueryDefs collection, but after it, it will. CurrentDB, on the other hand, refreshes all collections each time it is called, so you never have to worry about refreshing any of your collections.
DBEngine(0)(0) returns the internal pointer the Access Jet workspace uses to point to the database currently open in the Access UI. CurrentDB returns a copy of the database structure, and each call to CurrentDB creates a new copy. Thus, CurrentDB will use more memory, because it creates a copy of the structure that points to the database currently open in the Access UI, while DBEngine(0)(0) uses no additional memory, because it returns not a copy, but simply a pointer to an existing memory structure.
Likely the refreshing of the collections is the reason why CurrentDB is "1700%" slower (or whatever the number was), but probably some of the extra time is taken up by the process of setting up the copy of the database object, as well.
Again, none of this makes any difference in actual coding practice, as you just don't need to constantly be opening and closing pointers to the database currently open in the Access UI, as IT'S NOT BEING OPENED AND CLOSED CONSTANTLY.
So, is this a potaeto/potahto thing?
No, because there's one "bug" in DBEngine(0)(0) that could cause it to return an unexpected database pointer (though it would actually be technically correct), and that is in certain contexts immediately after an Access wizard has run, DBEngine(0)(0) will return a pointer to the wizard database, and not to the database currently open in the Access UI.
That is because there is a distinction between:
the database currently open in the Access UI, AND
the first database in the first workspace of the DBEngine object.
CurrentDB, on the other hand, always returns a reference to #1 and never to #2. DBEngine(0)(0), however, can return something else, as for a very brief moment, the wizard really is the first database in the first workspace of the DBEngine object right after the wizard is dismissed.
Now, is it likely that production code could ever encounter this error? Probably not, since it's unlikely that you'd use a wizard in a production app. But this could also apply to library databases, and that's not so uncommon a technique, particularly for advanced Access programmers.
If there were a practical performance difference, DBEngine(0)(0) might be worth it, but since there isn't, CurrentDB is preferable since it is 100% reliable in returning the expected database reference.
All that said, I don't use either in my apps.
Instead, I use a function that caches a database variable initialized with CurrentDB. This means I never have to initialize any database variables, just use my dbLocal() function in place of any database variable. Here's the code:
Public Function dbLocal(Optional bolCleanup As Boolean = False) As DAO.Database
' This function started life based on a suggestion from
' Michael Kaplan in comp.databases.ms-access back in the early 2000s
' 2003/02/08 DWF added comments to explain it to myself!
' 2005/03/18 DWF changed to use Static variable instead
' uses GoTos instead of If/Then because:
' error of dbCurrent not being Nothing but dbCurrent being closed (3420)
' would then be jumping back into the middle of an If/Then statement
On Error GoTo errHandler
Static dbCurrent As DAO.Database
Dim strTest As String
If bolCleanup Then GoTo closeDB
retryDB:
If dbCurrent Is Nothing Then
Set dbCurrent = CurrentDb()
End If
' now that we know the db variable is not Nothing, test if it's Open
strTest = dbCurrent.Name
exitRoutine:
Set dbLocal = dbCurrent
Exit Function
closeDB:
If Not (dbCurrent Is Nothing) Then
'dbCurrent.close ' this never has any effect
Set dbCurrent = Nothing
End If
GoTo exitRoutine
errHandler:
Select Case Err.Number
Case 3420 ' Object invalid or no longer set.
Set dbCurrent = Nothing
If Not bolCleanup Then
Resume retryDB
Else
Resume closeDB
End If
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in dbLocal()"
Resume exitRoutine
End Select
End Function
In code, you use this thus:
Dim rs As DAO.Recordset
Set rs = dbLocal.OpenRecordset("SQL SELECT statement")
[do whatver]
rs.Close
Set rs = Nothing
dbLocal.Execute("SQL INSERT statement")
Debug.Print dbLocal.OpenRecordset("SELECT @@IDENTITY")(0)
dbLocal.Execute("SQL UPDATE statement")
Debug.Print dbLocal.RecordsAffected
The first time you call it, it will initialize itself with CurrentDB and return the cached database object.
When you close the app, you call it with the bolCleanup flag set to TRUE so that it will clean up the cached variable.
If you add to the collections, they don't refresh (because you're not calling CurrentDB each time, just using a cached database variable that was initialized with CurrentDB), so you have to do this:
[add a new QueryDef]
dbLocal.QueryDefs.Refresh
And that's it. No global variables, no need to constantly initialize database variables with CurrentDB (or DBEngine(0)(0)). You just use it and stop worrying about it. The only technical detail is making sure that your app's shutdown routine calls dbLocal(False).
So, that's my take on DBEngine(0)(0) vs. CurrentDB.
A side issue about cleanup of database variables initialized by these two methods:
If you initialize a db variable with CurrentDB, you don't close it, just set it to Nothing:
Dim db As DAO.Database
Set db = CurrentDB
...
'db.Close <= don't do this
Set db = Nothing
If you do issue the db.Close, nothing at all will happen, neither bad nor good.
On the other hand, in this case:
Dim db As DAO.Database
Set db = DBEngine(0)(0)
...
'db.Close <= don't do this
Set db = Nothing
...issuing the db.Close can cause your app to crash in certain versions of Access.
Neither of them could actually work, because you can't close the database currently open in the Access UI via the Close method of a database object.
On the other hand, if you do this:
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase("path to external MDB file")
...
db.Close ' <=you *must* do this
Set db = Nothing
...you really do want to close it out, as it is an external database. That code can't be done with CurrentDB, because this is the only way to open a reference to another database.