Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

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

Jim Dettman (EE MVE)Volunteer
CERTIFIED EXPERT
Independent consultant specializing in the writing of custom packages for businesses.
Published:
Updated:
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
21,779 Views
Jim Dettman (EE MVE)Volunteer
CERTIFIED EXPERT
Independent consultant specializing in the writing of custom packages for businesses.

Comments (14)

Colin RiddingtonSelf employed Access developer

Commented:
Hi Jim
Thanks for the amazingly fast response

The test db I used contained 3 local tables (the large data table & 2 tables to store the results), 2 forms & some module code.

To clarify
#1 Set db=CurrentDB followed by db.Execute followed by Set DB = Nothing - done twice for populating the table filed & again when clearing it
#2 As above but with db=DBEngine(0)(0)
#3 No variable set/destroyed. Used CurrentDB.Execute
#4 No variable set/destroyed. Used DBEngine(0)(0).Execute

Tests #1 & #2 were mainly done in response to a point made by another user
It was #3 & #4 that I was most interested in. I normally use #3 in production databases

As I stated, I used update statements rather than looping through a recordset.
Looping would have been many times slower. In fact on my PC, with 2.6 million records, it probably wouldn't have completed at all

The tests were repeated many times.
All 4 tests gave similar results to each other.
Sometimes #3 using CurrentDB was fastest, other times #4 using DBEngine. Differences were always negligible

A colleague has just repeated the tests on a new, powerful PC.
All results were approx 20s compared to my 60s.

TestID      Workstation      TestType                  TestTime
1      COLIN-PC      Set CurrentDB            62.02
2      COLIN-PC      Set DBEngine(0)(0)              60.67
3      COLIN-PC      Use CurrentDB            61.99
4      COLIN-PC      Use DBEngine(0)(0)              62.03

5      WTP51            Set CurrentDB            20.09
6      WTP51                  Set DBEngine(0)(0)              21.4
7      WTP51            Use CurrentDB            20.13
8      WTP51                  Use DBEngine(0)(0)              20.07

For comparison I've just repeated the same tests on the same PC in a split database with a large number of databases objects with the same data in a linked Access table. Obviously the times are significantly slower but for info I got:

TestID      Workstation      TestType                  TestTime
9      COLIN-PC      Set CurrentDB            200.03
10      COLIN-PC      Set DBEngine(0)(0)              201.7
11      COLIN-PC      Use CurrentDB            199.88
12      COLIN-PC      Use DBEngine(0)(0)              201.67

For info, I've also published my earlier results at two Access forums AWFUA.

The consensus of the replies (close to 100%) has been to use CurrentDB.
Two replies quoted this MS article from Aug 2017:
https://docs.microsoft.com/en-us/office/VBA/api/Access.Application.CurrentDb

The most important part of the article in this context was:
Note In previous versions of Microsoft Access, you may have used the syntax DBEngine.Workspaces(0).Databases(0)or DBEngine(0)(0) to return a pointer to the current database. In Microsoft Access 2000, you should use the CurrentDb method instead. The CurrentDb method creates another instance of the current database, while the DBEngine(0)(0)syntax refers to the open copy of the current database. The CurrentDb method enables you to create more than one variable of type Database that refers to the current database. Microsoft Access still supports the DBEngine(0)(0)syntax, but you should consider making this modification to your code in order to avoid possible conflicts in a multiuser database.
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Author

Commented:
A couple of comments:

1.  On this:

The consensus of the replies (close to 100%) has been to use CurrentDB.
Two replies quoted this MS article from Aug 2017:
https://docs.microsoft.com/en-us/office/VBA/api/Access.Application.CurrentDb
....

 Well that's more or less exactly what I said with the article and why you can have the best of both worlds.   With a routine like I posted, you can avoid the overhead of CurrentDB() and get the overhead only when you need it.    And that routine was nothing new when I wrote this up....you could find code like this in many places.   I think Michael Kaplan was the first poster with one, and he later went on to work for Microsoft on Access.  I think if there was anything wrong on this, he would have updated his page on it.

 Also, while the article is from Aug 2017, I happen to know this text:

Note In previous versions of Microsoft Access, you may have used the syntax DBEngine.Workspaces(0).Databases(0)or DBEngine(0)(0)to return a pointer to the current database. In Microsoft Access 2000, you should use the CurrentDb method instead.

 is a carry forward from the article that existed back in the A2000 days when Microsoft started going in a different direction with some things.  As a result, it became more critical to use CurrentDB() rather than dbEngine as people had done in years past, which many apps were still carrying.

2. On this:

The test db I used contained 3 local tables (the large data table & 2 tables to store the results), 2 forms & some module code.

  That is on the small side and not what I would call a typical app.  You have 6 - 9 objects depending on how the code is being stored.  

  What might be un-clear in the article is that the results you get will vary based on the number of objects you have.   I said "as much as", not that you would see that kind of difference across the board.   If you do this timing on a typical app (one with a couple hundred objects or more), you should start noticing a significant difference.

3. I am actually not the source of the statistic.   It first appeared in the Access 2000 Developers Handbook, published by Sybex, which was  authored by Mike Getz, Paul Litwin, and Mike Gilbert.

  They worked closely with the Access team at that time on the book, so I have little doubt about the accuracy.

  I will say though that things may have changed with later versions (I wrote this up back in 2009 after all), but I have continued to use this technique in my own apps and I consistently notice a difference in the speed.   However I have not actually timed it since back then.

  So give it a go on something that has more than a handful of objects in it and post the results back.  Also, the inserts are not really required.   All your trying to time is the difference in the call to CurrentDB() vs dbEngine, so just putting them in a loop and setting a DB variable is the only test you need.  That will make the timing a little more accurate and the testing easier to do as well.

 I'd do it myself just out of curiosity, but I've got more than enough normal work to keep me busy at the moment.   I will put it on my to-do list to checkout though if you don't post back.

Jim.
Colin RiddingtonSelf employed Access developer

Commented:
Jim

When I read your article, I had expected the tests to show some difference.
They didn't. That was rather disappointing.

I've used my test app for various other speed tests and its proved very effective in identifying code that speeds up or slows down applications. Having said that, I accepted your point about the test app not being a real world scenario.

So, as already mentioned, I repeated the tests using a much larger UK Postal Addresses split database which I have developed as a commercial app.
It has 3 Access BEs - the largest of which is 1.5GB mainly due to the postocdes table which I used for testing
Thankfully that database is used as reference data only - its never written to by users.
Otherwise, I would have converted it to a SQL backend

The statistics for that application are:

Database statistics for the test app
That is, as far as hope you'll agree, a real world app

I gave the results in my last post - all around 200s.
For info the tests were done in both Access 2010 32-bit & repeated in Access 2016 64-bit on a slightly faster laptop.

So what I'm saying is that both the tests I've done (repeated by a colleague on a fast PC) & the responses from other experienced developers at both UA and AWF all indicate the same thing. If using DBEngine offers any advantage at all, it seems to be so small that there is little point in even considering 'using the best of both worlds' as you put it.

However, as you seem convinced that there is a performance difference, it would be appreciated if you could find the time to provide an example that shows this clearly.

Thanks

Colin
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Author

Commented:
That is, as far as hope you'll agree, a real world app

 That's closer, but you also keep talking about the number of records, which for this is irrelevant.  What matters is the number of objects in the DB container.

 And keep in mind, this was written almost ten years ago...Access was different back then as well as PC's and this was old news back when I wrote this.   What was true back then might not be true today given that eighteen years have passed since A2000.

 I haven't even thought about this in a long time....I've just used it in all my apps since then.

Jim.
Colin RiddingtonSelf employed Access developer

Commented:
Jim

The whole point of my last post was to say the results were still very similar in a database with a large number of objects.

Anyway, I've modified the tests to add 10,000 records to a table in 10 loops of 1000 each

#1 Set db=CurrentDB followed by db.Execute followed by Set DB = Nothing - after clearing the data & again after each 1000 record loop
#2 As above but with db=DBEngine(0)(0)
#3 No variable set/destroyed. Used CurrentDB.Execute in each step
#4 No variable set/destroyed. Used DBEngine(0)(0).Execute in each step

I repeated each test  5 times and calculated the averages

Speed Test Results
As you can see, there's still not a huge difference in values between the tests
BUT overall method #1 using Set db=CurrentDB is faster than method #2 using Set db = DBEngine(0)(0)
Similarly #3 using CurrentDB.Execute is very slightly faster than method #4 using DBEngine(0)(0).Execute

And of course there is still the need to factor in refreshes as necessary using DBEngine(0)(0)

And keep in mind, this was written almost ten years ago...Access was different back then as well as PC's and this was old news back when I wrote this.   What was true back then might not be true today given that eighteen years have passed since A2000.

 I haven't even thought about this in a long time....I've just used it in all my apps since then.

Exactly the point I've been trying to make throughout.
I'm happy to accept your point about performance benefits using DBEngine(0)(0) may well have been true once.
However, so far I've seen no evidence that is still the case now.

I'm attaching my new test db - feel free to modify the tests if you still think these aren't appropriate

Cheers

Colin
SpeedComparisonTests---NEW.zip

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.