It is not clear what you mean by the term "overhead", so I don’t know how someone could answer your question as formulated.
But the topic DBEngine (0) (0) and CurrentDB has been discussed quite widely over the years in Access newsgroups. I have long since calmed down using CurrentDB, so I will summarize the situation as I see it.
DBEngine (0) (0) is much faster than CurrentDB in this code:
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 remember correctly, ADH97 said that DBEngine (0) (0) was something like 17 times faster.
But look at this code - it does not test anything useful. Remember that both CurrentDB and DBEngine (0) (0) return pointers to the database currently open in the access user interface (with some caveats below for DBEngine (0) (0)). There is no place in the Access application where either of these loops will be useful anyway. 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) can be 1700% faster in a loop, THIS DOESN'T MATTER, because you will never return the link to the database currently open in Access UI, there will be enough time for the difference quite insignificant (we say milliseconds here, although, of course, CurrentDB will take longer for databases with a large number of objects).
So, first of all, before I explain why there is a difference, you must first recognize that the difference in performance is completely insignificant, since the only circumstances in which it can exceed the most trivial differences is a circumstance that would be dead brain stupid code .
Now why the difference?
Well, there are two main reasons:
DBEngine (0) (0) returns collections because they were initialized when the database currently open in the user interface was first opened if you did not manually update the collections. So, if you add a new saved QueryDef so that it is available in code using DBEngine (0) (0), after adding a new QueryDef you should call
DBEngine (0) (0) .QueryDefs.Refresh
. Until then, your new query will not be in QueryDefs, but after that it will. CurrentDB, on the other hand, updates all collections every time it is called, so you never have to worry about updating any of your collections.DBEngine (0) (0) returns an internal pointer that uses the Access Jet workspace to point to the database currently open in the access user interface. 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 user interface, while DBEngine (0) (0) does not use additional memory, since it does not return a copy , but just a pointer to an existing memory structure.
Probably updating collections is the reason that CurrentDB is "1700%" slower (or regardless of quantity), but it is likely that some extra time is taken by the process of setting up a copy of the database object as well.
Again, none of this has anything to do with actual coding practice, since you just do not need to constantly open and close pointers to the database currently open in the access user interface, since DO NOT OPEN AND CLOSE CONSTANTLY.
So is this a potaeto / potahto thing?
No, because there is one error in DBEngine (0) (0) that can lead to the return of an unexpected database pointer (although this will be actually technically correct), and this happens in certain contexts right after the access wizard, DBEngine (0) ( 0) will return a pointer to the wizard database, and not to the database currently open in the access user interface.
This is because there is a difference between:
database currently open in the access user interface, 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, may return something else, since for a very short moment the wizard is really the first database in the first DBEngine object workspace immediately after the master rejects.
Now, is it possible that production code might ever encounter this error? Probably not, since you are unlikely to use the wizard in a production application. But this may also apply to library databases, and it is not so unusual, especially for advanced Access programmers.
If there was a practical performance difference, DBEngine (0) (0) might be worth it, but not there, CurrentDB is preferred because it is 100% reliable in returning the expected database reference.
All that said, I do not use in my applications.
Instead, I use a function that caches a database variable initialized with CurrentDB. This means that I do not need to initialize any database variables, just use the dbLocal () function instead of any database variable. Here is 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 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 it like this:
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
On the first call, it initializes itself with CurrentDB and returns a cached database object.
When you close the application, you call it with the bolCleanup flag set to TRUE to clear the cached variable.
If you add to collections, they are not updated (because you do not call CurrentDB every time, just using the cached database variable that was initialized with CurrentDB), so you should do this:
[add a new QueryDef] dbLocal.QueryDefs.Refresh
What is it. There are no global variables, there is no need to constantly initialize database variables using CurrentDB (or DBEngine (0) (0)). You just use it and stop worrying about it. The only technical information is to make sure your application shutdown program calls dbLocal (False).
So, I take DBEngine (0) (0) against CurrentDB.
The question is about clearing database variables initialized with these two methods:
If you initialize the db variable with CurrentDB, you do not 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 choose db.Close, nothing 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 db.Close may cause your application to crash in certain versions of Access.
None of them can work, because you cannot close an open database in the access interface through the Close method of the 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 want to close it, as it is an external database. This code cannot be executed using CurrentDB, because this is the only way to open a link to another database.