<

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

Published on
34,223 Points
16,823 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
  • 6
  • 4
  • 3
  • +1
14 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 60

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

Expert Comment

by:John Clark
I realize I am a little late to the party here, hopefully the OP is still around!

Very detailed explanation as to the difference and very cool code. One question in regards to these statements:

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

and

"When you look at all the CurrentDB() calls in your application, you will begin to realize that this can add up."

As a rule, I always use Dim db AS DAO.Database and set it to the CurrentDB. Additionally, my exit handler closes all recordsets and sets the rs and db to Nothing.

Does this not suffice in freeing up resources?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

LVL 60

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
It frees up the object variable, but every time you call CurrentDB() again, your incurring overhead that typically 99% of the time is not needed.

Jim.
0

Expert Comment

by:John Clark
So you are still around! Impressive...

Thanks for the explanation and I will take your word for it and will implement this into my projects. Thanks for providing this article.
0
LVL 60

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
So you are still around! Impressive...

Thanks.   I've been here on EE since 1999 and on CompuServe six years before that, so I've been answering Access questions for twenty five years now.  Seems like yesterday....but that's another story.

Thanks for the explanation and I will take your word for it and will implement this into my projects. Thanks for providing this article.

  It's an easy and quick change (drop the function in, then do a find and replace for CurrentDB() with CurDB()), and you will see a difference.  This is typically what I do on an app I didn't write.  Then as I work on code, I eliminate the setting of a database object variable and just call CurDB() directly.

Jim.
0

Expert Comment

by:John Clark
It's an easy and quick change (drop the function in, then do a find and replace for CurrentDB() with CurDB()), and you will see a difference.

A difference I DID see: "28 -Out if Stack Space" and then a system exit!

Did a google on the error and came back and re-read your last post:
Then as I work on code, I eliminate the setting of a database object variable and just call CurDB() directly.

I went back and removed all my "Set" and "Dim" statements in regards to database objects and now everything is fine and dandy like Christmas Candy.

I had 47 references to the CurrentDb Function. I had NO idea...
0

Expert Comment

by:Colin Riddington
I almost always just use CurrentDB.Execute so I rarely bother with the Set db commands

Having read this article & the various comments, all of which I found very interesting, I thought I'd test the time taken for different scenarios to complete

I used a large postcodes table of 2.6 million records and measured the time taken to update null records in a text field to 'OK' and to clear those same records.

I used 4 methods
1. Set db=CurrentDB before each update statement, used db.Execute & Set db=Nothing afterwards
2. Set db=DBEngine(0)(0) before each update statement, used db.Execute & Set db=Nothing afterwards
3. Omitted the Set db and used CurrentDB.Execute
4. Omitted the Set db and used DBEngine(0)(0).Execute

I repeated each test several times compacting then restarting before each group of tests.
Outcomes were very similar in each case

NEWResults.png
I certainly see no evidence that using DBEngine is faster than CurrentDB on this test .l. . et alone 5000 times faster.
Would using a loop with an append query to add millions of records to a table in groups of say 100 per loop give different outcomes?
Or can you suggest a better test to perhaps prove your point

Bearing in mind issues touched on in the article about the need to refresh using DBEngine(0)(0) and the often significant time that would need, I think these limited tests have, so far at least, confirmed method 3 to be the best approach overall
0
LVL 60

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Did your DB contain anything other than tables?   From your test #'s, I would think not.

 Again, CurrentDB() is returning a new database object through Access where as dbEngine is a JET reference to the DB.  Although they appear similar, they are not the same and can in fact be different.

 As part of returning a new database object, a refresh of all the collections is done.  If your DB contains a large number of objects, that refresh can be substantial.  However if you have a container with only a table or two, then it's not and you won't see much of a difference.

 The other thing with your testing is the difference between #1 and #3; one does not typically set a database variable inside a loop. The fact that you did is why the times are so close.

 But the whole point of using a variable is to avoid that.

  So #1 should have been:

 Set db = CurrentDB()

 Do while
     ' insert
 Loop

Set db = Nothing

I'm a bit suspicious of test #4 as well, because it should have been faster than #2 because of the additional overhead of creating a variable and clearing it.   There's not much of a difference there, but there should have been some and #4 should not have been greater than #2.  It also should not have been greater than #3, so I'm left wondering if something is not being accounted for in your testing (although it should like you did address everything).

Jim.
0

Expert Comment

by:Colin Riddington
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.
0
LVL 60

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
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.
0

Expert Comment

by:Colin Riddington
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
0
LVL 60

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
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.
0

Expert Comment

by:Colin Riddington
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
0

Featured Post

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Join & Write a Comment

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month