Cleaning up object variables - MVPs please

[Jamey Shuemaker]

I've seen multiple threads (several in the last 6 months or so) on this
topic, but I wanted to clarify my practices. I understand the need for
cleaning object variables by setting them to Nothing and/or closing
them, but I'm not sure about the order.

As I understand it all object variables that I create like:

Set rs = SomeExistingRec ordset

need to be cleaned up via:

rs.Close
Set rs = nothing

Those I didn't create like:

Set db = CurrentDB

or

Set db = DBEngine(0)(0)

need to be cleaned only via:

Set db = Nothing

1) Is the order of the Close method and the Set statement important?

2) Are these practices adequately freeing the memory used by the object
variables?

3) Is the Close unnecessary for object variables like Recordsets,
Connections, and other pointers of the sort?

4) How should pointers to Containers be handled?

Thanks in advance for revisiting a very old topic.

[Lyle Fairfield]

Re: Cleaning up object variables - MVPs please

You can pretty well clean up MVP code by removing every third line as
redundant and every third line (of what's left) as a manifestation of
entrenched superstition. Take the remainder and remove every third line
of that as archaic. What's left is often pretty good stuff that isn't
as objectionable as your subject line at all.

[Keith Wilby]

Re: Cleaning up object variables - MVPs please

"Lyle Fairfield" <lylefairfield@ aim.comwrote in message
news:1159354462 .761031.150790@ i3g2000cwc.goog legroups.com...

What's left is often pretty good stuff that isn't
as objectionable as your subject line at all.
>

I thought it was just me who'd taken offense. Apparently not.

Keith.

[pietlinden@hotmail.com]

Re: Cleaning up object variables - MVPs please


Keith Wilby wrote:

"Lyle Fairfield" <lylefairfield@ aim.comwrote in message
news:1159354462 .761031.150790@ i3g2000cwc.goog legroups.com...

What's left is often pretty good stuff that isn't
as objectionable as your subject line at all.

>
I thought it was just me who'd taken offense. Apparently not.
>
Keith.

I guess if I were smarter, I might take offense at someone implying
that such a simple question could only be answered by an MVP. It's one
of those MLH-like posts, which could easily be answered by looking it
up here. Or is an MVP designation a prerequisite to having brains? I
thought lots of MVPs were pretty sharp even before they were
designated... shows what I know!!!

[David W. Fenton]

Re: Cleaning up object variables - MVPs please

"Jamey Shuemaker" <cantankeris@ya hoo.comwrote in
news:1159345499 .824879.95900@d 34g2000cwd.goog legroups.com:

I've seen multiple threads (several in the last 6 months or so) on
this topic, but I wanted to clarify my practices. I understand the
need for cleaning object variables by setting them to Nothing
and/or closing them, but I'm not sure about the order.
>
As I understand it all object variables that I create like:
>
Set rs = SomeExistingRec ordset
>
need to be cleaned up via:
>
rs.Close
Set rs = nothing

That all depends.

If you do:

Set rs1 = [open the recordset]

then:

Set rs = rs1

When you close rs, you'll be closing rs1.

If you didn't open rs1, then you shouldn't close it, such as:

Set rs = Me.RecordsetClo ne

Those I didn't create like:
>
Set db = CurrentDB
>
or
>
Set db = DBEngine(0)(0)
>
need to be cleaned only via:
>
Set db = Nothing

That's because you can't close the database currently opened in the
Access UI.

However, for a database variable initialized from CurrentDB, closing
it causes no problem (it also has no effect), but for one
initialized with DBEngine(0)(0), it can cause problems if you close
it.

1) Is the order of the Close method and the Set statement
important?

Yes. If you set it to Nothing, you won't be able to close it,
because the memory has already been released. Actually, the pointer
to the memory has been released, but the memory may still be in use.
That's why you close and then set to Nothing.

2) Are these practices adequately freeing the memory used by the
object variables?

Should be.

3) Is the Close unnecessary for object variables like Recordsets,
Connections, and other pointers of the sort?

Dunno about Connections, since those are ADO objects, but for
recordsets, it's essential to close and then set to Nothing. ADO
boosters claim that ADO does not have the memory leaks that come
with DAO and VBA, but I wouldn't trust it myself.

4) How should pointers to Containers be handled?

I would set them to Nothing.

You should also set collection item variables to Nothing if you've
used a For/Each loop:

For Each ctl In Me.Controls
[whatever]
Next ctl
Set ctl = Nothing

Michael Kaplan recommended doing this because it's conceivable for
an implicit reference to the last control in the collection to have
been left unreleased.


--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Randy Harris]

Re: Cleaning up object variables - MVPs please

On 27 Sep 2006 03:54:22 -0700, "Lyle Fairfield"
<lylefairfield@ aim.comwrote:

>You can pretty well clean up MVP code by removing every third line as
>redundant and every third line (of what's left) as a manifestation of
>entrenched superstition. Take the remainder and remove every third line
>of that as archaic. What's left is often pretty good stuff that isn't
>as objectionable as your subject line at all.

ROFL

[RLN]

Re: Cleaning up object variables - MVPs please


David,
Are you saying that this code below closes all controls (Recordsets,
databases, etc.) if they happen to be open and destroys them?

<begin code>
For Each ctl In Me.Controls
[whatever]
Next ctl
Set ctl = Nothing
<end code>

(I want to make sure that my application is cleaned up completely before
closing to prevent corrupting anything.)

Thank you.



*** Sent via Developersdex http://www.developersdex.com ***

[Terry Kreft]

Re: Cleaning up object variables - MVPs please

Lyle,
Should you be answering this post?

I know I shouldn't (and therefore I haven't), which is a shame because even
with my non-MVP status I'm prety sure I could have a fair crack at it.


--

Terry Kreft


"Lyle Fairfield" <lylefairfield@ aim.comwrote in message
news:1159354462 .761031.150790@ i3g2000cwc.goog legroups.com...

You can pretty well clean up MVP code by removing every third line as
redundant and every third line (of what's left) as a manifestation of
entrenched superstition. Take the remainder and remove every third line
of that as archaic. What's left is often pretty good stuff that isn't
as objectionable as your subject line at all.
>

[Stephen Lebans]

Re: Cleaning up object variables - MVPs please

Real funny Randy.
PLONK!

--

HTH
Stephen Lebans

http://www.lebans.com

Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


"Randy Harris" <randy@no.spam. netwrote in message
news:tv2lh250hf omn61ofnmr3fgdo l6r2b0d6t@4ax.c om...

On 27 Sep 2006 03:54:22 -0700, "Lyle Fairfield"
<lylefairfield@ aim.comwrote:
>

>>You can pretty well clean up MVP code by removing every third line as
>>redundant and every third line (of what's left) as a manifestation of
>>entrenched superstition. Take the remainder and remove every third line
>>of that as archaic. What's left is often pretty good stuff that isn't
>>as objectionable as your subject line at all.

>
ROFL

[Stephen Lebans]

Re: Cleaning up object variables - MVPs please

I give up Lyle.
PLONK!

--

Stephen Lebans

http://www.lebans.com

Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


"Lyle Fairfield" <lylefairfield@ aim.comwrote in message
news:1159354462 .761031.150790@ i3g2000cwc.goog legroups.com...

You can pretty well clean up MVP code by removing every third line as
redundant and every third line (of what's left) as a manifestation of
entrenched superstition. Take the remainder and remove every third line
of that as archaic. What's left is often pretty good stuff that isn't
as objectionable as your subject line at all.
>

[Lyle Fairfield]

Re: Cleaning up object variables - MVPs please

Terry Kreft wrote:

Lyle,
Should you be answering this post?
>
I know I shouldn't (and therefore I haven't), which is a shame because even
with my non-MVP status I'm prety sure I could have a fair crack at it.

Stupid me! I was going to add, "except for MVP Terry Kreft, of course."
but then I thought, "Why state the self-evident?". So you can see that
I'm totally confused about posting in this thread and what I posted. Oh
well ... no one agrees with me anyway ... I clean up DAO databases and
recordsets (which I don't use any more or almost never use) and nothing
else, although sometimes I post stuff from way back that releases other
objects. Works for me; I suppose everyone should work at his/her level
of comfort.

And if you charge by the byte, it makes sense to have a lot of lines.
I've used "What sign are you?", "I'll cook you dinner if you'll cook me
breakfast", "I have only three months to live.", "Do you know how to
use a whip?" a lot. I just declare a string variable and set it to
point to one of them. It doesn't do anything and that extra size makes
the client feel he/she is getting his/her money's worth.

[Randy Harris]

Re: Cleaning up object variables - MVPs please

On Wed, 27 Sep 2006 15:24:33 GMT, "Stephen Lebans"
<ForEmailGotoMy .WebSite.-WWWdotlebansdot ...@linvalid.co mwrote:

>Real funny Randy.
>PLONK!

I guess it's safe to assume that Stephen isn't offended by a request
for answers from MVPs only.

[Lyle Fairfield]

Re: Cleaning up object variables - MVPs please

Stephen Lebans wrote:

I give up Lyle.
PLONK!

How surprising. Didn't Cambronne say,
La veille Garde muert, mais elle ne se rend pas.
?

Well, others quote him as saying
Merde!
which is also appropriate here I suppose.

[David W. Fenton]

Re: Cleaning up object variables - MVPs please

"Stephen Lebans"
<ForEmailGotoMy .WebSite.-WWWdotlebansdot ...@linvalid.co mwrote in
news:7JwSg.4106 1$9u.351678@urs a-nb00s0.nbnet.nb .ca:

I give up Lyle.
PLONK!

I plonked Lyle a long time ago. Keeps my blood pressure lower.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Re: Cleaning up object variables - MVPs please

RLN <nospam@devdex. comwrote in
news:451a8f24$0 $25782$815e3792 @news.qwest.net :

Are you saying that this code below closes all controls
(Recordsets, databases, etc.) if they happen to be open and
destroys them?
>
><begin code>
For Each ctl In Me.Controls
[whatever]
Next ctl
Set ctl = Nothing
><end code>
>
(I want to make sure that my application is cleaned up completely
before closing to prevent corrupting anything.)

Recordsets and databases are not controls.

The For/Each loop I posted would be something you might do in a
form. It applies to any collection that you can loop through with a
For/Each construct. The object variable used (in this case, ctl, of
type Control) gets an implicit Set ctl = ... statement in each
iteration of the loop. On the last iteration, that ctl variable is
left pointing to the last control in the form's Controls collection.
Setting it to Nothing insures that no implicit pointer is left
hanging.

To repeat, the example applies to any collection that you loop with
a For/Each construct.

It has nothing to do with cleaning up other variables.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/