Applies to: All versions of Access Level: Intermediate
One of the things all of us tend to loose sight of is that what we think of "Access", is really a couple of different components working closely together. Access is made up of a user interface and objects (forms, reports, and macros), JET, which is the default database engine, and Visual Basic for Applications (VBA), which provides a coding language. Because of this, there are certain things that you would expect to work somewhat the same, and yet are very different.
One of these areas is the use of CurrentDB() vs. dbEngine.Workspaces(0).Dat
abases(0) (or dbEngine(0)(0) for short). While the two seem similar in that they both give a reference to a object for the current database, they are not the same.
CurrentDB() is an Access function and is processed by the UI layer, which returns a new reference to the current database. As a result, it's always up to date with current information (i.e. it would include a form that was just added) in its collections. Using dbEngine(0)(0) on the other hand is getting a pointer to the DAO (Data Access Object) reference maintained by JET. In using this reference you are bypassing the UI layer, which means that you won't know if something has been done in the UI unless you do a refresh of a collection. That refresh can be quite expensive in terms of performance.
So why would you want to use a dbEngine(0)(0) reference at all instead of CurrentDB()? Because it is considerably faster; as much as five thousand times faster. When you look at all the CurrentDB() calls in your application, you will begin to realize that this can add up.
The reason for this and which is not at all obvious (and you may have caught in the statement above), is that each call to CurrentDB() returns a
new database object.
At this point, you may be thinking "Great, I'll use dbEngine(0)(0) everywhere and if I really need updated collections, I'll just refresh when I need to." After all, a lot of applications don't add objects at runtime, so the refresh issue may not seem like such a big deal.
However dbEngine(0)(0) does have one other problem that you need to be aware of; in certain situations, it may not point to the same database that CurrentDB() does. Keep in mind that one is the current database the UI is working with, while the other is the current database that JET is working with. The situations would be fairly rare where they might be different (they may occur when using wizards, libraries, or compacting DBs accomplished by closing and re-opening db's in code), but they can occur.
The work around:
So how might one get the best of both worlds and yet still be safe? With one small and easy to routine:
Private objCurDB As DAO.Database
Public Function CurDb() As DAO.Database
If objCurDB Is Nothing Then
Set objCurDB = CurrentDb()
Set CurDb = objCurDB
Paste the above into a general module and now in place of CurrentDB(), you can use CurDB(). Since CurDB() uses a variable to hold a reference to the current DB, it is fast like using dbEngine, but it is being done through the UI, so the reference will always be to the correct DB.
But since we are now always referring to the same database object (it's been cached in objCurDB), that leaves us with the issue that the collections will not be refreshed at each call. That however can easily be solved by making a slight change:
Public Function CurDb(Optional bolRefresh As Boolean = False) As DAO.Database
If objCurDB Is Nothing Or bolRefresh = True Then
Set objCurDB = CurrentDb()
Set CurDb = objCurDB
Now call it with a true argument and you'll have a refreshed database object. And if you really want to keep things neat and tidy and clean up after yourself (and one always should), you would want to add:
Public Sub CurDBClear()
Set objCurDB = Nothing
And call it when closing your app.
I should point out that all this is nothing new and there are numerous postings out on the net similar to what I have posted above. However I still find many applications don't use this technique. Also, this question seems to crop up time and time again along with the various issues associated with it on Experts Exchange, so I thought I summarize the topic a bit and post a tip & trick.
Some would also argue that it's just best to always use CurrentDB() and be done with it, as the only time this would really become an issue performance wise is if you used CurrentDB() in a loop (which one typically would never do). While to a certain extent that is true, I do find apps using this routine to be "snappier" then those without it. Using this technique also reduces memory consumption to a small extent. Given both of those and how simple it is to use, I would respond "Why not?"