Would you make common variables public?

[Banana]

It just occurred to me that I use certain variables in several of my routines. Maybe you've done the same... something like:

Dim db as DAO.Database
Dim rst as DAO.recordset
Dim qdf as DAO.QueryDef

Now, if it's re-used in many routines, wouldn't it make more sense to make it a public variable, and set them to whatever you need it for a certain routine? Would it be more efficient than having to re-initializing and discarding it for every routine that used it? What problem would one expect from making them public?

Also, since it's very common to set db = CurrentDb, would it also make sense to set it publicly, and for cases where you aren't using CurrentDb, use a private variable just for that one?

 

[CraigDolphin]

I might get nervous about that especially if I have functions calling other functions....might be a potential for the rst variable or qdf variable to be messed up in a function if it is set again in a function called by the first. Not sure of this...just waffling off the top of my head

 

[boblarson]

I HAVE done that before, but it does take some good management to make it work.

 

[RuralGuy]

IMHO public and/or global variables make code more difficult to maintain. I like code where everything is very close by and understandable. I try and code so someone else can maintain it 5 years later. I don't always get there, but I try.

 

[RoyVidar]

Perhaps this discussion in one of the public Access NGs, can give some inputs? http://groups.google.com/group/micr...lesdaovba/browse_frm/thread/d52ff0f20df3510d/

Most of the replies discusses using either a function or a property to keep a persistent pointer to the current database, in stead of a public variable. One of the pros, is that this will reinitialize the pointer should a code reset occur.

 

[Banana]

RoyVidar, thanks for the find. I missed that in my search (wrong keywords, I guess).

Interestingly, the NG seems to be focused on merits of making CurrentDb() public to all routines, but doesn't discuss whether one would want to do same for recordsets, querydefs or maybe even common integers used for counting a For..Next loop.

CraigDolphin is spot on about the problems of same variables being accidentally re-used between calls. In case of CurrentDb(), I know that if I were to refer to other database other than the local database, I would definitely want to use a special variable for readability purposes so I can see this less of a problem. But for other common variables that may have different purposes between calls, I suppose one could just initalize a local variable when the public variables are in use but that requires manual management and may somehow obfuscate the code. (Why is he talking about rst2 which is dimmed in this routine but not about rst which isn't even dimmed at all?!?)

Any more opinions?

 

[The_Doc_Man]

My question is specific to this case:

If your code is like:

Dim DB as DAO.database
...

set db = CurrentDB
...

ThisDBName = db.name

Then why wouldn't you have just omitted the declaration and used

ThisDBName = CurrentDB.name

?

For the other issue, when you DO NOT intend to use CurrentDB but rather will open another DB than the one hosting the VBA code in question, here is the issue.

If you EVER have to use re-entrancy for its intended purpose then remember that a private, non-global reference is re-declared on each call. FURTHER, if this code happens to be RECURSIVE, the reference is re-declared on each call. Therefore, your choice of local/private vs. global/public depends on whether you needed to use the reference per entrance or globally across all calls. There is no "generally better practice" in the sense that the generally better practice is intent-dependent. The answer changes based on what you wanted to do.

So in that somewhat pedantic sense, the question is unanswerable without specifying the conditions first.

 

[Banana]

If all I needed was a name (or any other property), then yes, I could just directly reference those.

But the case is more like this:

I try to be explicit in my references whenever I use a recordset, a querydef, and in most of cases, I need to access several properties associated with those variables. Therefore my code may look like:

Dim db as DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.Querydef

Set db = CurrentDb()
Set qdf = db.QueryDefs("MyQueryName")

With qdf
    !Parameter1 = Variable1
    !Parameter2 = Variable2
    !Parameter3 = VAriable3
End With

Set rst = qdf.OpenRecordset

... 
'Manipulate rst and whatnots
...

Set rst = nothing
Set qdf = nothing
Set db = nothing

This is true in several of routines, and to my mind, if I'm doing same thing over, I should make it public to several shared routines because not only it's more efficient, but also more portable and less error-prone.

I hope this clears up the case I'm thinking about.

I'm afraid I didn't understand the part about re-entrancy.