<

CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) and an alternative

Published on
33,724 Points
16,324 Views
14 Endorsements
Last Modified:
Awarded
Community Pick
Jim Dettman (Microsoft MVP/ EE MVE)
Independent consultant specializing in the writing of custom packages for businesses.
Applies to: All versions of Access    Level: Intermediate

Introduction:

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).Databases(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.

The problem:

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()
 End If
 
 Set CurDb = objCurDB

End Function

Open in new window


  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()
 End If
 
 Set CurDb = objCurDB

End Function

Open in new window


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

End Sub

Open in new window


And call it when closing your app.

In summary...

  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?"

Jim Dettman
14
Comment
2 Comments
LVL 29

Expert Comment

by:IrogSinta
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

Hey Jim,
Very informative article!  I just wanted to get a bit of clarification on what you mentioned here.  Would using CodeDB then be the same as dbEngine(0)(0) since the scope of CodeDB is local to wherever the running code is from?

Ron
0
LVL 59

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
<<I just wanted to get a bit of clarification on what you mentioned here.  Would using CodeDB then be the same as dbEngine(0)(0) since the scope of CodeDB is local to wherever the running code is from?>>

It would depend on what you were doing.  I think you would find that if you referred back to something in your main DB, then dbEngine would be different then CodeDB().

Current/CodeDB always points to the database where the code is currently executing, but dbEngine is the JET reference to whatever DB it is taking to at that moment.

 I've never used library databases all that much, so this is an area I've never gotten into too deeply.  I'm just aware that they can be different.

Jim.
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month