StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
1837922
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
4
CommunityOwnedDate
CreationDate
2009-12-03T05:52:34.307
FavoriteCount
0
LastActivityDate
2010-09-29T20:35:28.583
LastEditDate
2010-09-29T20:35:28.583
LastEditorUserId
9787
OwnerUserId
9787
ParentId
1833746
PostTypeId
2
Score
16
ViewCount
0
LastEditorDisplayName
text
Body
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. <h3>DBEngine(0)(0) is far faster than CurrentDB in this code:</h3> <pre><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() </code></pre> 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: <pre><code> 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 </code></pre> 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: <ol> <li>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 <pre>DBEngine(0)(0).QueryDefs.Refresh</pre>Before 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.</li> <li>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.</li> </ol> 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: <ol> <li>the database currently open in the Access UI, AND</li> <li>the first database in the first workspace of the DBEngine object.</li> </ol> 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: <pre><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 </code></pre> In code, you use this thus: <pre><code> 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 </code></pre> 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: <pre><code> [add a new QueryDef] dbLocal.QueryDefs.Refresh </code></pre> 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: <pre><code> Dim db As DAO.Database Set db = CurrentDB ... 'db.Close <= don't do this Set db = Nothing </code></pre> If you do issue the db.Close, nothing at all will happen, neither bad nor good. On the other hand, in this case: <pre><code> Dim db As DAO.Database Set db = DBEngine(0)(0) ... 'db.Close <= don't do this Set db = Nothing </code></pre> ...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: <pre><code> Dim db As DAO.Database Set db = DBEngine.OpenDatabase("path to external MDB file") ... db.Close ' <=you *must* do this Set db = Nothing </code></pre> ...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.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POMS Access: Is there a significant overhead when using CurrentDB as opposed to DBEngine(0)(0)?
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USDavid-W-Fenton
UserOwnerUserId
1. USDavid-W-Fenton
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POMS Access: Is there a significant overhead when using CurrentDB as opposed to DBEngine(0)(0)?
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.