dbEngine(0)(0) vs Currentdb

[shadow9449]

After reading this thread, here's my non-scientific contribution:

I have a routine in my program that was calls a function that was declaring db as CurrentDB at the beginning of the function. I tested this function setting the routine to do a task that would call the function about 2600 times. It took 45 seconds. Then I tried it by calling a global function that declared a public variable pointing to CurrentDB only once and it took about 31 seconds. This was just done on my local workstation, not on a network. But it does show a big difference at least anecdotally.

 

[NauticalGent]

I realize this thread is old and the marks deep on this dead horse. But I came across this article ( https://nolongerset.com/demystifying-doevents/ ) from one of our newest members, @NoLongerSet.

I think it adds a nice finish to this subject.

 

[Galaxiom]

I have a routine in my program that was calls a function that was declaring db as CurrentDB at the beginning of the function. I tested this function setting the routine to do a task that would call the function about 2600 times. It took 45 seconds. Then I tried it by calling a global function that declared a public variable pointing to CurrentDB only once and it took about 31 seconds. This was just done on my local workstation, not on a network. But it does show a big difference at least anecdotally.

Big difference? 5 ms per loop?

BTW The "overheads" from running CurrentDb() are not just about about setting and destroying variables. My understanding is that CurrentDb() also refreshes the database Collections before creating the pointer so that any changes made to the schema in Access are reflected in calls to it.

Hence setting a single persistent variable and reusing it skips this step, in effect going back to being the same as using DbEngine(0)(0).

In most cases it isn't going to matter because the schema isn't altered but the tiny delay is inconsequential. Mircosoft recommends always using CurrentDb().

 

[shadow9449]

Big difference? 5 ms per loop?

Percentage-wise it's a significant difference. Perhaps per iteration it's small but when your scope is big enough it will add up.

I did say that it's non-scientific

 

[isladogs]

I realize this thread is old and the marks deep on this dead horse. But I came across this article ( https://nolongerset.com/demystifying-doevents/ ) from one of our newest members, @NoLongerSet.

I think it adds a nice finish to this subject.

Actually I think it fits really well with this article on my website: DoEvents vs DBIdle.RefreshCache

 

[CJ_London]

Mircosoft recommends always using CurrentDb().

And what about if your want to know the recordsaffected value?

 

[The_Doc_Man]

I think many of us understand that Microsoft's recommendations are to be taken cum grano. They recommend the Edge browser, too, and for a while they even recommended Internet Explorer before Edge was a thing. It's hard to tell when M$ makes a recommendation whether it really IS a good utility - or merely good for their bottom line.