Closing Variables
Hatman50
how Variables should be handled. Specifically to do with closing them
or "freeing" them
I see the follwoing in lots of code and would like detailed
explanation for a presentation i am preparing.
rs.close
set rs=Nothing
var=""
Thanks
David W. Fenton
news:5c3e4155-d8b9-44f2...@c13g2000vbr.googlegroups.co
m:
You don't "close" variables, but you can clear the values assigned
to them.
Object variables like a recordset variable store pointers to
structures in memory, and you have to do two things to clean up:
1. close the structure pointed to with rs.Close
2. clear the pointer to that structure with Set rs = Nothing
The last example you show, var="" would be setting a string variable
back to a zero-length string (better would be var = vbNullString),
but this isn't necessary the same way cleaning up object variables
is.
The reason you have to clean up object variables is because they
store references to memory structures. VBA keeps track of whether
the memory structures can be cleaned by counting the references to
them. When you close a recordset and set its variable to Nothing,
you're clearing out the references to that memory structure that the
particular variable you're acting on had. If there are no other
references, the memory will be cleared.
Now, theoretically, when a recordset variable goes out of scope, its
references should be released. That is, the rs.Close and Set rs =
Nothing should happen implicitly. However, because of memory leaks
in some parts of VBA (particular when used with DAO), this doesn't
happen because VBA somehow doesn't keep the reference count properly
updated, so the variables don't go out of scope, the memory is not
released, and you can have any number of odd problems as a result.
So, instead of depending on garbage collection happening implicitly
according to scope, we clean up all the structures we've created and
the variables that point to them.
String variables, numeric variables, date variables and variants do
not have this problem, as they are not pointers to other structures
(though they are pointers to somewhere in memory).
Basically, if you use Set to initialize an object variable or the
New keyword in the variable's declaration, you need to clean it up
when you're done with it. Variables that you initialize with simply
variable = [value] do not need to be cleaned up in this fashion.
It's important, though, to note that there are some cases where
implicit references are created, and you need to clean up there to
be on the safe side. For Each loops are one such case:
Dim ctl As Control
For Each ctl in Me.Controls
Debug.Print ctl.Name & ": " & ctl.Value
Next ctl
Set ctl = Nothing
In this case, the For Each loop creates an implicit reference to
each control as it loops through the collection. But only the last
one can be left hanging, so you only need to set to Nothing after
you're outside the loop.
There are other more esoteric implicit references, but I've already
gone on too long.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
Hatman50
wrote:
> Hatman50 <hatma...@gmail.com> wrote innews:5c3e4155-d8b9-44f2...@c13g2000vbr.googlegroups.co
Thanks so much for going on as long as you have! That certainly helps.
Tony Toews
<NoE...@SeeSignature.invalid> wrote:
>It's important, though, to note that there are some cases where
>implicit references are created, and you need to clean up there to
>be on the safe side. For Each loops are one such case:
>
> Dim ctl As Control
>
> For Each ctl in Me.Controls
> Debug.Print ctl.Name & ": " & ctl.Value
> Next ctl
> Set ctl = Nothing
>
>In this case, the For Each loop creates an implicit reference to
>each control as it loops through the collection. But only the last
>one can be left hanging, so you only need to set to Nothing after
>you're outside the loop.
I don't think I've ever closed a control variable like that. Maybe I
should be but ...
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
David W. Fenton
news:9178961ea1cloqr6p...@4ax.com:
> On 16 Sep 2010 03:07:49 GMT, "David W. Fenton"
><NoE...@SeeSignature.invalid> wrote:
>
>>It's important, though, to note that there are some cases where
>>implicit references are created, and you need to clean up there to
>>be on the safe side. For Each loops are one such case:
>>
>> Dim ctl As Control
>>
>> For Each ctl in Me.Controls
>> Debug.Print ctl.Name & ": " & ctl.Value
>> Next ctl
>> Set ctl = Nothing
>>
>>In this case, the For Each loop creates an implicit reference to
>>each control as it loops through the collection. But only the last
>>one can be left hanging, so you only need to set to Nothing after
>>you're outside the loop.
>
> I don't think I've ever closed a control variable like that.
> Maybe I should be but ...
Michael Kaplan recommended it, though he did say it was a belt and
suspenders situation and highly unlikely to be an issue.
Keep in mind also that the reference issues are most common with DAO
data types. ADO, for instance, does not have this problem, so you
can get away without cleaning up your ADO connection and recordset
objects. Were I to ever use ADO, though, I'd clean up anyway.