Access 2003 - Close vs. Nothing

[Nick Giordano]

Hi,
Upon conversion to Access 2003, some DAO modules have both recordset close
and set "recordset name" = nothing statements. Since Access 2003,
automatically closes these recordsets upon exiting the program, are these
needed in Access 2003? If so, should the Close or Nothing statement be
used?
Thank you,
Nick

 

[Douglas J. Steele]

Nothing wrong with keeping both statements, rather than relying on it
occurring by default.

All versions of Access were supposed to close recordsets and uninstantiate
them as well.

 

[Nick Giordano]

Hi Doug,
Are both necessary? If so, is order important, e.g. Close than set to
Nothing?
Thank you,
Nick

 

[Albert D. Kallal]

Hi Doug,
Are both necessary? If so, is order important, e.g. Close than set to
Nothing?
Thank you,
Nick

Well, setting to nothing would mean at that point the close method would not
work any more (it would fail, as the
recordset would not be open anymore!!).
.....

Just from a logic point of view, to clean up...one would presumably close,
and then set the var = nothing.

as for both being necessary? Well, as mentioned, it has been a time honoured
and standard practice for years.

I still do both, much out of habit..but, as a coding practice, it still is
recommend...

 

[Egbert Nierop \(MVP for IIS\)]

Hi,
Upon conversion to Access 2003, some DAO modules have both recordset close
and set "recordset name" = nothing statements. Since Access 2003,
automatically closes these recordsets upon exiting the program, are these
needed in Access 2003? If so, should the Close or Nothing statement be
used?
Thank you,
Nick

Hi,

In the COM world, within Scripting, VBA or VB6, it is a myth that you must
do it.
In fact, in the past, when there were leaks in the ADO-COM objects, you had
to explicitely clean up. Currently, the unmanaged destructors and
smartpointers that C++ programmers use, garantuee, that COM resources, are
not waisted.

Setting an object to nothing, while the End Sub or the 'end of scope' is
near, is really a bogus statement.
Closing a recordset, while the End of Scope is near, is not necessary as
well.

I'm sure, that some purists now will hit and slam me!

 

[Allen Browne]

Egbert, it's not so much the purists as those with experience in Access.

Douglas Steele remembers the Access 97 days, when we all found ourselves
with database that we could not close. Access would just minimize to the
task bar when you tried to close it, so killing it with Ctl+Alt+Del was the
only way. Failure to set Recordset variables to Nothing ended up being one
of the causes. It was a hellava job to debug these databases, so many of us
became super cautious about cleaning up after ourselves.

AFAIK, the issue was fixed years ago, but I'm still not trusting VBA to get
this right 100% of the time. It just wasn't worth the effort NOT to do it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Egbert Nierop \(MVP for IIS\)]

Egbert, it's not so much the purists as those with experience in Access.

Douglas Steele remembers the Access 97 days, when we all found ourselves
with database that we could not close. Access would just minimize to the
task bar when you tried to close it, so killing it with Ctl+Alt+Del was
the only way. Failure to set Recordset variables to Nothing ended up being
one of the causes. It was a hellava job to debug these databases, so many
of us became super cautious about cleaning up after ourselves.

AFAIK, the issue was fixed years ago, but I'm still not trusting VBA to
get this right 100% of the time. It just wasn't worth the effort NOT to do
it.

That's true.

Today, I know no such products
Especially, since ADO was rewritten using good COM practices.

( A lot of old MS platform SDK code, reveals that many C++ programmers,
loved to write their own IUnknown implementations which were not thread
safe)

 

[Douglas J. Steele]

That's true.

Today, I know no such products
Especially, since ADO was rewritten using good COM practices.

( A lot of old MS platform SDK code, reveals that many C++ programmers,
loved to write their own IUnknown implementations which were not thread
safe)

Of course, most of us continue to use DAO with Access, since it's superior
to ADO interacting with Jet.

 

[Guest]

But when writing ASP pages, it is still necessary, because
(some kinds of) ASP pages don't go out of scope, and you
can use up all your memory very quickly.

Which is one reason why many of the MS examples show
closing and setting to nothing: the samples are valid for ASP
as well as VB, VBA, and VBS.

Also, in Access 2000+ you have to be careful inside the
before-update and after-update events of a form, because
error handling doesn't work correctly there, and you can loose
objects (although a total Access crash is more likely)..

On the other hand, indiscriminately 'closing' objects can be
just as bad. Attempting to 'close' instances of CodeDB or
CurrentDB can detach an open recordset, preventing Access
from closing it, and preventing Access from closing.

(david)