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 = SomeExistingRecordset

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]

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]

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.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]

Keith Wilby wrote:

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.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]

"Jamey Shuemaker" <ca*********@yahoo.comwrote in
news:11*********************@d34g2000cwd.googlegro ups.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 = SomeExistingRecordset

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.RecordsetClone

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]

On 27 Sep 2006 03:54:22 -0700, "Lyle Fairfield"
<ly***********@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]

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]

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" <ly***********@aim.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.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]

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" <ra***@no.spam.netwrote in message
news:tv********************************@4ax.com...

On 27 Sep 2006 03:54:22 -0700, "Lyle Fairfield"
<ly***********@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]

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" <ly***********@aim.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.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]

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]

On Wed, 27 Sep 2006 15:24:33 GMT, "Stephen Lebans"
<[e-mail]:

>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]

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]

"Stephen Lebans"
<[e-mail] in
news:7J*******************@ursa-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]

RLN <no****@devdex.comwrote in
news:45***********************@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/

[Jamey Shuemaker]

Et al, above:

I didn't mean you "couldn't" comment if you "weren't" an MVP. Obviously
if you've seen fit to yammer about how offensive that is, you're likely
someone who could or would answer the questions above.

Please excuse my obvious jackassery, and go on yammering.

RLN wrote:

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 ***

[Tim Marshall]

Jamey Shuemaker wrote:

Et al, above:

I didn't mean you "couldn't" comment if you "weren't" an MVP. Obviously
if you've seen fit to yammer about how offensive that is, you're likely
someone who could or would answer the questions above.

S'alright, but you hurt our feelinsg.... 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Lyle Fairfield]

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

Well, that explains everything! I couldn't understand why some of my
mapi/nntp posts returned Success_Success (0). They must have been the
ones that raised your blood pressure.
Thanks for explaining that.

[Bas Cost Budde]

Hey, MLH is not all bad!

Guys, if I replied to all posts I take offense, you'd ban me for spam. I
think it is not trivial to find the balance between gentle but firm
correction, and outright corner-swacking. At least for me it wouldn't be
trivial.

Maybe we should do a little more pointing to netiquette documents? There
are several, and I like them all.

http://www.mvps.org/access/netiquette.htm

pi********@hotmail.com schreef:

>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!!!

--
Bas Cost Budde
Holland

[Bas Cost Budde]

Yammering? That's almost Dutch. I'm always happy to learn words like
these. :) Is the term sea-related in any way, that you know?

I guess your addition 'MVPs please' didn't quite effect what you had in
mind when you added it, and I am genuinly curious as to what that is.

Jamey Shuemaker schreef:

Et al, above:

I didn't mean you "couldn't" comment if you "weren't" an MVP. Obviously
if you've seen fit to yammer about how offensive that is, you're likely
someone who could or would answer the questions above.

Please excuse my obvious jackassery, and go on yammering.

--
Bas Cost Budde
Holland

[Jamey Shuemaker]

As I said in the original post, I've viewed the threads of the past
several months and others from long ago. Many of the posts on the topic
concern A97 or A2K. My chief interest was what the party line is, now,
on such things....whether things have changed in the recent past with
regard to the release of memory from object variables.

I certainly didn't mean to hurt anyone's feelings, and I don't really
think I did. This is, afterall, not the religion or politics board, so
no one should be losing much sleep over anything said, here.

I attached the "MVP please" to the title, not to dissuade others with
intelligent (or more importantly, those of practice and usage) opinions
on the topic, but to see where those whose knowledge of the software is
endorsed by the software provider stand on the topic....again, the
party line.

Regards, and thanks for all the valuable corrections above. I'll steer
clear of the use of anything so disdaneful as "MVP" in future posts.

Thanks to Mr. Fenton, as well. Copy-all.
Bas Cost Budde wrote:

Yammering? That's almost Dutch. I'm always happy to learn words like
these. :) Is the term sea-related in any way, that you know?

I guess your addition 'MVPs please' didn't quite effect what you had in
mind when you added it, and I am genuinly curious as to what that is.

Jamey Shuemaker schreef:

Et al, above:

I didn't mean you "couldn't" comment if you "weren't" an MVP. Obviously
if you've seen fit to yammer about how offensive that is, you're likely
someone who could or would answer the questions above.

Please excuse my obvious jackassery, and go on yammering.

--
Bas Cost Budde
Holland

[Keith Wilby]

"Bas Cost Budde" <b.*********@dev.null.comwrote in message
news:ef**********@localhost.localdomain...

Yammering? That's almost Dutch. I'm always happy to learn words like
these. :) Is the term sea-related in any way, that you know?

In the UK, "yammering" is a slang term to mean talking aimlessly ...
"yapping" and "gobbing off" are other examples. The OP would appear to be
in Texas, so perhaps the US version has a similar meaning, it would
certainly fit in this thread ;-)

Keith.

[Terry Kreft]

Lyle,
No, that was OK I'm not an MVP anymore (and haven't been for a year or so
now).

I agree with you sometimes, I don't agree with you sometimes as well.

The thing that concerns me though is how much damage you're doing to that
cheek that you seem to have your tongue stuck in so often <g>.
--

Terry Kreft
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

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.

[Terry Kreft]

No that was me. He plagiarised me despite the copyright notice<g>.

But see
http://www.napoleon-series.org/resea...cambronne.html or
another chain of events.
--

Terry Kreft
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

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.

[Terry Kreft]

Yammering, is an English word but
http://www.yourdictionary.com/ahd/y/y0004700.html

.... say's' it may well have a Flemish origin.

--

Terry Kreft
"Bas Cost Budde" <b.*********@dev.null.comwrote in message
news:ef**********@localhost.localdomain...

Yammering? That's almost Dutch. I'm always happy to learn words like
these. :) Is the term sea-related in any way, that you know?

I guess your addition 'MVPs please' didn't quite effect what you had in
mind when you added it, and I am genuinly curious as to what that is.

Jamey Shuemaker schreef:

Et al, above:

I didn't mean you "couldn't" comment if you "weren't" an MVP. Obviously
if you've seen fit to yammer about how offensive that is, you're likely
someone who could or would answer the questions above.

Please excuse my obvious jackassery, and go on yammering.

--
Bas Cost Budde
Holland

[Bas Cost Budde]

I know the term, apart from the human usage, for the sound the wind
makes around objects. Most notably at sea.

Terry Kreft schreef:

Yammering, is an English word but
http://www.yourdictionary.com/ahd/y/y0004700.html

... say's' it may well have a Flemish origin.

--
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html

[David W. Fenton]

"Jamey Shuemaker" <ca*********@yahoo.comwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:

As I said in the original post, I've viewed the threads of the
past several months and others from long ago. Many of the posts on
the topic concern A97 or A2K. My chief interest was what the party
line is, now, on such things

What would lead you to believe that things have changed since A2K?

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

[David W. Fenton]

"Lyle Fairfield" <ly***********@aim.comwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:

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.

Your straw man is on fire.

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

[Lyle Fairfield]

Bas Cost Budde wrote:

Hey, MLH is not all bad!

I admire your patience. I hope you will be able to help him with his
recent request:

"trying to use mapisendmail in an offline mode"

and

"Can I use MAPISendMail to write email to OE's Outbox folder rather
than actually attempting the SEND"

I think these are about the same thing.

[Tim Marshall]

Bas Cost Budde wrote:

Maybe we should do a little more pointing to netiquette documents? There
are several, and I like them all.

http://www.mvps.org/access/netiquette.htm

It's been done. Corner-swacking is the necessary next stage of
treatment, I think. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[CDMAPoster]

Terry Kreft wrote:

No that was me. He plagiarised me despite the copyright notice<g>.

But see
http://www.napoleon-series.org/resea...cambronne.html or
another chain of events.
--

Terry Kreft

What he probably meant was (from Google Translate):

Le fil est mort, mais il ne s'arrête pas . :-)

(The thread has died, but it does not stop.)

>

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

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.

James A. Fortune
CD********@FortuneJames.com

I don't give no [merde]. I don't take no [merde]. And when [merde]
comes around I don't back away from it. -- Amanda Jo

[Bas Cost Budde]

Honestly - I never tried :D

My patience is based largely upon two facts:

* I am not getting paid for posting here
* I have children

Lyle Fairfield schreef:

Bas Cost Budde wrote:

>Hey, MLH is not all bad!

I admire your patience. I hope you will be able to help him with his
recent request:

"trying to use mapisendmail in an offline mode"

--
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html

[Jamey Shuemaker]

if i could figure a way to get my oxford english dictionary CD to run
on XP, i'd tell you what it says there about its origin. "Yammering",
as used above, is as summarized by Mr. Wilby. the etymology of such a
word in the aforementioned nautical sense is certainly plain,
especially if the word you understand as "yammering" is Dutch, as I
think you indicated earlier.

"Jackassery" is the real gem in all of that. THAT is a truly special
word that is part idiomatic, part vulgar dialect, and certainly
self-deprecatory in this sense.
Bas Cost Budde wrote:

I know the term, apart from the human usage, for the sound the wind
makes around objects. Most notably at sea.

Terry Kreft schreef:

Yammering, is an English word but
http://www.yourdictionary.com/ahd/y/y0004700.html

... say's' it may well have a Flemish origin.

--
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html

[Jamey Shuemaker]

excellent point.
David W. Fenton wrote:

"Jamey Shuemaker" <ca*********@yahoo.comwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:

As I said in the original post, I've viewed the threads of the
past several months and others from long ago. Many of the posts on
the topic concern A97 or A2K. My chief interest was what the party
line is, now, on such things

What would lead you to believe that things have changed since A2K?

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

[David W. Fenton]

"Jamey Shuemaker" <ca*********@yahoo.comwrote in
news:11**********************@m73g2000cwd.googlegr oups.com:

David W. Fenton wrote:

>"Jamey Shuemaker" <ca*********@yahoo.comwrote in
news:11**********************@h48g2000cwc.googleg roups.com:

As I said in the original post, I've viewed the threads of the
past several months and others from long ago. Many of the posts
on the topic concern A97 or A2K. My chief interest was what the
party line is, now, on such things

What would lead you to believe that things have changed since
A2K?

excellent point.

Then why did you ask the question in the first place? Did you ask
the question you really needed answered? What led you to ask the
question you did ask?

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

[Jamey Shuemaker]

Then why did you ask the question in the first place? Did you ask

the question you really needed answered? What led you to ask the
question you did ask?

curiosity. just wanted to see if I was wasting time killing objects
that would be cleaned up by their procedural scope, or if they were
still thought to persist beyond that. the only time i've had any
trouble with object variables outliving their calling procedure or
class module has been with user defined class objects. in practice i
dispose of all object variables by setting them to Nothing.