CurrentDB vs DBEngine(0)(0)
Last Updated 27 Feb 2019 Difficulty level : Moderate
This is the second in a series of articles discussing various tests done to compare the efficiency of different approaches to coding.
Example databases are provided in each case so the same tests can be done on your own workstations
This was written in response to the dbEngine(0)(0) vs Currentdb thread started by Access World Forums member John Clark (NauticalGent).
John had found an article by ex-MVP Jim Duttman written in Dec 2009: CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) and an alternative
In the article, Jim wrote:
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 article was written in 2009 but the author had repeated the same statements in recent replies to the article.
Although there is a lot of good advice in the article, I was surprised by his recommendation to mainly use dbEngine(0)(0).
By contrast, Microsoft have been advising the use of CurrentDB rather than DBEngine(0)(0) from around 2000 onwards!
For example, see this Microsoft article which was last updated 8 Feb 2022.
I was also very doubtful about the supposed speed increase (up to 5000x) with the use of dbEngine(0)(0).
So I decided to test the two methods and ran the following tests for comparison:
1. Set db=CurrentDB followed by db.Execute followed by Set db = Nothing at the end
This was done repeatedly - both after clearing the data & again after each 1000 record loop
2. As test 1 above but using db=DBEngine(0)(0)
3. Set db=CurrentDB ONCE at the start and Set db = Nothing ONCE at the end
4. As test 3 above but with db=DBEngine(0)(0)
5. Used CurrentDB.Execute in each step. No variable set/destroyed.
6. Used DBEngine(0)(0).Execute in each step. No variable set/destroyed.
I repeated each test 10 times and calculated the averages
The first set of results below are for an old desktop PC
Desktop i5 processor 2.90GHz ; 4GB RAM ; 32-bit Access:
These are the same results as a report
As expected, the differences were mostly fairly small but some patterns are clear.
1. Setting the variable db=CurrentDB once or repeatedly is faster than using CurrentDB.Execute
2. Similarly setting db=DBEngine(0)(0) once or repeatedly is faster than using DBEngine(0)(0).Execute
3. It makes little difference whether the variable is set once or repeatedly
4. Overall CurrentDB is slightly faster than DBEngine(0(0) though the differences are small
Factoring in the additional time needed to refresh the data if using dbEngine(0)(0), the assertion in the article that dbEngine(0)(0) is preferable and much faster is clearly incorrect.
Using CurrentDB outperforms DBEngine(0)(0).
For best results, use Set db=CurrentDB then db.Execute instead of CurrentDB.Execute
I repeated the tests on two other devices as a further check.
Laptop i5 processor 2.60GHz ; 8GB RAM ; 64-bit Access
These are the same results as a report
Tablet Intel Atom processor 1.33GHz; 2GB RAM ; 32-bit Access
Based on these tests:
a) Overall the desktop is faster than the tablet despite having less RAM (though it does have a slightly faster CPU)
b) The underpowered tablet struggles on all tests but the last two tests are dramatically slower.
I only ran these tests twice as they were so painfully slow
c) CurrentDB is generally faster than DBEngine(0)(0) - the exact opposite of what was said in the article that triggered this thread.
I contacted the author of the article but unfortunately he just tried to invalidate the results obtained.
Click to download: CurrentDB vs DBEngine Comparison Tests v5.2 Approx 1.2 MB (zipped)
Colin Riddington Mendip Data Systems Last Updated 14 Feb 2022