One Database Object Across Whole File (MS Access)

Question

TL;DR: Is there a good case against declaring a database object in a public module, initializing it on application launch, and keeping it until the database closes (i.e., using the OnClose event of a hidden form), instead of declaring/initializing/destroying a database object in every single sub/function wherein such is needed?

---

The rest:

Thus far in my experience with Access, I've used:

Dim db as DAO.Database
Set db = CurrentDB()

...any time I needed to do recordset work on a database, change database properties, etc., which of course necessitates not forgetting to:

Set db = Nothing

...in the Sub/Function cleanup. Both of these. Every. Cotton. Picking. Time.

According to info I was able to find on the 'net, directly using CurrentDb.Anything is inadvisable, due to its ephemeral nature past the line of code wherein it executes (returning something more like a one-time value that expires in most cases after its initial use).

But I'm a garden-variety lazy human being, and declaring/initializing/destroying database objects over and over really singes my nerves after a while.

Answer 1

Well, if you deploy as a compiled accDE? Then this idea is ok.

However, if you run a accDB? Then any un-handled error in the application will blow out ALL global and local vars. In a accDE? Even errors, even un-handled ones NEVER blow out global or local variables.

Needless to say as a result, deploying compiled accDE have a amazing increase in reliability of the application deployed.

In most cases, I just recommend to use CurrentDB. And as a general coding practice, global vars are not great, since then how can you move + pull code between different applications without introduction of global dependences in such code.

So, there not a lot of advantages here (sticking to local vars). And as noted, if any stray error occurs (without a error trap), then if this is a accDB then all global values are lost. But as noted, with a accDE, it don't matter, and all and any global scoped variables will ALWAYS retain their values not matter what happens (they persist for the lifetime of the session, or from when the application was launched).

So there not really a significant case to define a global db var, and it can be lost based on above rules.

I have seen some suggestions that using CurrentDB can use up some additional memory, but then again, I not seen this as a big issue.

So I think there are more reasons to not adopt this idea (global db var), then there are not. As noted, pulling forms or module code between applciations now means that a global var dependency exists, and that does suggest somewhat less code re-use.

My view unless a really good case exists for using a global var, then don't.

So from what I can see? There are more downsides to using that global var then there are advantages. Certainly advantages exist, but so do downsides. So, do the advantages outweigh the downsides of doing this?
So far, I can't say this is the case (for a global var).

However, as always, particular use cases may well make the case better for that global var. So far, I not seen the coin side of the global var outweigh the downsides of doing so.

This is not one of those you must always do this one way, or do this the other way kind of deal. There not really a 100% one correct answer here. However, there are benefits and downsides to each approach.

My view is developer time is costly, and code re-use and forms and code that is "more" independent and not needing external global vars is a better coding practice.

And like so many things, global vars have their place, and use. But, as a general rule, avoiding global vars in code is a good strategy , and one that's not always a practical choice, yet still is a over all desirable goal to shoot for when ever possible.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 2

Hi David. Not trying to contradict Albert, but perhaps you could also take a look at Ben's blog on self-healing objects. Just a thought...

self-healing-objects