Setting variables to Nothing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Murphy - 17 Mar 2005 04:36 GMT

In my applications I have followed the practice of setting database and
recordset variables to nothing when exiting a function or subroutine,
for example:

Sub MySub()

    Dim db As Database
    Dim rst As Recordset

    ....

    rst.close
    set rst = nothing
    set db = nothing

End Sub

Is this also necessary for variables such as those listed below:

    Dim qdf as Querydef
    Dim prm as Parameter
    Dim fso As Object ' file system object
    Dim fs As Object ' file search object
    Dim ap As Application

Bill

Reply to this Message

Ken Snell [MVP] - 17 Mar 2005 04:54 GMT

Depending upon how you use them, and how you set them to a specific object,
the answer is.. maybe.

If you use
Set xxxx = something

then the answer is yes.

If you use
For Each xxxx in Something

then the answer is maybe. If you can exit the For loop without exiting
through the Next xxxx, then I would set xxxx to Nothing before the code
ends. Otherwise, the end of the loop will do it for you.

Signature

Ken Snell
<MS ACCESS MVP>

--

Ken Snell
<MS ACCESS MVP>

> In my applications I have followed the practice of setting database and
> recordset variables to nothing when exiting a function or subroutine, for
[quoted text clipped - 22 lines]
>
> Bill

Reply to this Message

Mark - 17 Mar 2005 04:58 GMT

I am of the opinion (and the practice) that if you explicitly "set" a
variable like "Set db = CurrentDB", you should always set it equal to
"nothing" when you're done with it (preferably somewhere in the error
handling section of your code just in case an error interrupts your
programming).
With something like:
Dim qdf as Querydef
For Each qdf In db.Querydefs
blah, blah
Next qdf
there's no need to set qdf=nothing when you're done with it.

> In my applications I have followed the practice of setting database and
> recordset variables to nothing when exiting a function or subroutine, for
[quoted text clipped - 22 lines]
>
> Bill

Reply to this Message

david epsom dot com dot au - 17 Mar 2005 06:21 GMT

There are three reasons to set something to nothing:

1) You are a C programmer.
C did not have memory management. Programmers had
to do it themselves. Good programmers were good at
memory management: bad programmers were bad a memory
management. C programmers looked at BASIC code and thought
they were looking at very bad C: they looked at BASIC
programmers and thought they were looking at very bad
C programmers.

2) You are a very old programmer.
You learned to program in a language where there
was no local scope, or it was difficult to track the
scope of variables, so you learned to always clear
variables and release memory after use, and re-initialise
before use.

3) You are afraid that your programming language
was built by C programmers, who at best thought that
memory management was your problem, and at worst were
having problems with memory management and variable
scope themselves.

In BASIC, including VBA, variables are always initialised
at creation, and pointer references are always cleared
when they go out of scope, ie for local variables, on exit
from a subroutine. Setting a local rs object to nothing
immediately before it is automatically set to nothing
is useful only if you think there is a bug in the automatic
system, or if you choose to worry about the appearance
of your code.

(david)

> In my applications I have followed the practice of setting database and
> recordset variables to nothing when exiting a function or subroutine, for
[quoted text clipped - 22 lines]
>
> Bill

Reply to this Message

Tim Ferguson - 17 Mar 2005 23:06 GMT

> Setting a local rs object to nothing
> immediately before it is automatically set to nothing
> is useful only if you think there is a bug in the automatic
> system,

There lies the rub. Old versions of VB and VBA definitely did leak memory
when some objects were not explicitly brought down. On the other hand,
nowadays I rarely Set stuff to Nothing and I am not aware of my computer
being badly affected by it; and ICBA to go looking for any bad effects.

All the best

Tim F

Reply to this Message

david epsom dot com dot au - 18 Mar 2005 05:12 GMT

> There lies the rub. Old versions of VB and VBA definitely did leak

I wanted to indulge in a little polemics, so I didn't go into
detail, but even historically, setting a recordset to nothing
immediately before it was automatically set to nothing was
never important. What was (and perhaps still is) important
was CLOSING a recordset before it was automatically set to
nothing, and CLOSING a recordset before it was reused (as in
a loop, or when using global variables).
http://support.microsoft.com/default.aspx?scid=kb;en-us;209847

An object should automatically close when it's reference count
goes to zero, but this is a delayed process, and if there is a
problem in the reference counting (a lost reference), then
explicitly closing the object is necessary.

Some objects don't have a close method, and the only way to
get them to close is to reduce the reference count to zero.
For these objects, there is no fix for circular references
or lost references, and the only solution is to avoid problems
(for example, not directly referring to checkbox controls in
VB If statements).

Circular references can be broken by setting the references
to nothing IF THEY ARE EXPOSED, which is a minor reason why
people get into the habit of setting their references to nothing.
(The IMPORTANT reason is because they are working in an environment
that does not automatically set their references to nothing)
The DAO error object may create circular references (?), but
they are not exposed: none of the built in objects expose
circular references that you could break by setting an object
to nothing.

Regarding 'appearances' (part of my concluding remarks), some
people ALWAYS set a reference to nothing if they have previously
set it to something. The MS style for VB code fragments now is
to use "set RS=nothing". This is a coding practice. I regard it
as a bad coding practice in a well designed Access application,
but only because I adopt a different coding practice: I never use
module-level variables. I regard module-level variables as bad
coding practice, and I think that they should be avoided in all
but the most rare circumstances.

In a well designed Access application, listing your variables
at the end of each subroutine is redundant, adds to code size
and code complexity, and is therefore wrong. However, there
are different coding styles, and if you want to list all or
some of your local variables at then end of each subroutine,
then I would only say: it is good to be consistent: find a style
and stick with it :~).

(david)

>> Setting a local rs object to nothing
>> immediately before it is automatically set to nothing
[quoted text clipped - 9 lines]
>
> Tim F

Reply to this Message

Marshall Barton - 18 Mar 2005 07:13 GMT

In a perfect world, I would agree with you David. However,
I've been bitten by the "Access Won't Close" problem too
many times in the past to put so much faith in **all** the
lost reference bugs being fixed (or that new ones will never
be created).

Besides, I am a firm believer in clearing a reference as
soon as I am done using it, even if it is at the end of the
procedure's logic. Who knows, I may forget to add it when I
have to modify the procedure sometime in the future. But,
as you say, this is a coding style issue. And, yes I am an
old, old time C programmer. Old habits and old dogs kind of
applies too ;-)

Signature

Marsh
MVP [MS Access]

>> There lies the rub. Old versions of VB and VBA definitely did leak
>
[quoted text clipped - 56 lines]
>> nowadays I rarely Set stuff to Nothing and I am not aware of my computer
>> being badly affected by it; and ICBA to go looking for any bad effects.

Reply to this Message