He had found an article by Microsoft MVP Jim Duttman written in Dec 2009:
CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) and an alternative
In the article. Jim Duttman 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.
Although the article was written in 2009, the author Jim Duttman had repeated the same statements in recent replies.
Although there is a lot of truth in the article, I was surprised by his advice to mainly use dbEngine(0)(0) as Microsoft have been advising the use of CurrentDB rather than DBEngine(0)(0) from around 2000 onwards! For example, see this Microsoft article dated 06/08/2017.
I was also extremely doubtful about the supposed speed increase (up to 5000x) with the use of dbEngine(0)(0).
So I decided to compare the two methods and ran the following tests:
1. Set db=CurrentDB followed by db.Execute followed by Set DB = Nothing
This was done repeatedly - after clearing the data & again after each 1000 record loop
2. As test 1 above but with 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 results below are for a desktop i5 processor 2.90GHz ; 4GB RAM ; 32-bit Access:
Click any image to view a larger version ...
As expected, the differences are 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
3. It makes little difference whether the variable is set once or repeatedly
4. Overall CurrentDB is slightly faster than DBEngine(0(0)
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 contacted the author of the article but he repeatedly tried to invalidate the results.
So I repeated the tests on two other devices
Laptop i5 processor 2.60GHz ; 8GB RAM ; 64-bit Access:
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 by Jim Duttman that triggered this thread.
Click to download: CurrentDB vs DBEngine Comparison Tests v5.2 Approx 1.2 MB (zipped)
2. CurrentDB vs DBEngine(0)(0) Updated 27/02/2019
To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks