Vfrdtyky

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.

DBEngine(0)(0) is far 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 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:

1. 
   

   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.Refresh

   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.



2. 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:

1. the database currently open in the Access UI, AND




2. 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.