Do I need to set object = nothing?

[Paul H]

A typical chunk of code......

Set db = CurrentDb
Set rs = db.OpenRecordset("tblFoo")

<Do some stuff here>

'How much of the stuff below do I need?
'Do I need to close the recordset?
rs.Close
'Do I need to close the database object?
db.Close
'Do I need to set the recordset to Nothing?
Set rs = Nothing
'Do I need to set the database to Nothing?
Set db = Nothing

I have been doing it this way for years, is this the right way?

Thanks,

Paul

[Rick Brandt]

Paul H wrote:

A typical chunk of code......

Set db = CurrentDb
Set rs = db.OpenRecordset("tblFoo")

<Do some stuff here>

'How much of the stuff below do I need?
'Do I need to close the recordset?
rs.Close
'Do I need to close the database object?
db.Close
'Do I need to set the recordset to Nothing?
Set rs = Nothing
'Do I need to set the database to Nothing?
Set db = Nothing

I have been doing it this way for years, is this the right way?

Thanks,

Paul

If everything worked the way it should then you wouldn't need any of it.
There are enough places where things might not work as they should that a
good rule is "If you open it, then you should close it and if you set an
object to something then you should set it to nothing".

Even in cases where it is totally unnecessary it is never harmful to do your
own cleanup (that I'm aware of).

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Allen Browne]

It is good programming practice to close what you opened (by only what you
opened), and to dereference your objects.

In a perfect world, this would be unnecessary. Access would reliably close
anything you opened and dereference any objects you set as soon as the
procedure terminated and the objects went out of scope. We don't live in a
perfect world. There were bugs in Access 97, such that it was impossible to
close Access if you did not explicitly close the recordsets you opened.
Access would minimize to the taskbar when you tried to close it, and the
only solution was to use the task manager (Ctrl+Alt+Del) to kill it.

Even after Microsoft fixed that bug, later versions still had problems with
running out of databases if you did not explicitly set your database
variables to Nothing if they had already been set to CurrentDb(). Haven't
tried recently, but it was easy enough to do. Just run a loop that call a
function a few hundred times, and in the function set a Database type
variable to CurrentDb.

So, is it worth the effort to explicitly close what you opened and
de-reference your objects? Up to you, but the pain we had with the A97 bugs
(other causes as well as the Recordset one), and the difficulty of tracking
and fixing those left many of us rather fastidious about cleaning up after
ourselves instead of expecting the software to do it perfectly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul H" <no****@nospam.comwrote in message
news:W9********************@eclipse.net.uk...

>A typical chunk of code......

Set db = CurrentDb
Set rs = db.OpenRecordset("tblFoo")

<Do some stuff here>

'How much of the stuff below do I need?
'Do I need to close the recordset?
rs.Close
'Do I need to close the database object?
db.Close
'Do I need to set the recordset to Nothing?
Set rs = Nothing
'Do I need to set the database to Nothing?
Set db = Nothing

I have been doing it this way for years, is this the right way?

Thanks,

Paul

[Terry Kreft]

You've had good generalised answers from Rick and Allen but to your specific
question(s).

Yes
No
Yes
Yes

But if your ...
Set db = Currentdb
.... line had been ...
Set db = Opendatabase( ...

Then it would have been

Yes
Yes
Yes
Yes

--

Terry Kreft
"Paul H" <no****@nospam.comwrote in message
news:W9********************@eclipse.net.uk...

A typical chunk of code......

Set db = CurrentDb
Set rs = db.OpenRecordset("tblFoo")

<Do some stuff here>

'How much of the stuff below do I need?
'Do I need to close the recordset?
rs.Close
'Do I need to close the database object?
db.Close
'Do I need to set the recordset to Nothing?
Set rs = Nothing
'Do I need to set the database to Nothing?
Set db = Nothing

I have been doing it this way for years, is this the right way?

Thanks,

Paul

[David W. Fenton]

"Rick Brandt" <ri*********@hotmail.comwrote in
news:rc*******************@newssvr12.news.prodigy. com:

Even in cases where it is totally unnecessary it is never harmful
to do your own cleanup (that I'm aware of).

I think that if you set a db variable with CurrentDB() closing it
causes no problems, but if it was initialized with DBEngine(0)(0),
then it can cause a problem.

If you didn't open it, don't close it.

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