dereferencing recordsets and database

  • Thread starter Thread starter Jesper Fjølner
  • Start date Start date
J

Jesper Fjølner

I've read a lot of conflicting statements about the need for deferencing
objects.

If I use:
Dim db as DAO.database
Dim rs as DAO.recordset

Set db = currentdb
Set rs = db.OpenRecordset(SQL)

What's needed to clean up?

rs.close
set rs = nothing
set db = nothing

All of them? Can "rs.close" be skipped? I've heard that using "set
db=nothing" is just good practice and not necessary.
I guess that whatever causes bloat, memory leakage or instability needs to
be terminated properly.
If objects go out of scope automatically it's just good practice but not
strictly necessary.
What's the verdict? Thanks for any input.

Jesper Fjølner
 
Jesper said:
I've read a lot of conflicting statements about the need for deferencing
objects.

If I use:
Dim db as DAO.database
Dim rs as DAO.recordset

Set db = currentdb
Set rs = db.OpenRecordset(SQL)

What's needed to clean up?

rs.close
set rs = nothing
set db = nothing

All of them? Can "rs.close" be skipped? I've heard that using "set
db=nothing" is just good practice and not necessary.
I guess that whatever causes bloat, memory leakage or instability needs to
be terminated properly.
If objects go out of scope automatically it's just good practice but not
strictly necessary.
Click to expand...


While all that is supposed to be dealt with when the
procedure exits, do you really want to rely on the Access
developers having fixed every one of the many bugs in its
own cleanup logic from earlier versions?

With that in mind, the safest practice is to use all three.
The most important is to close the recordset when you are
done using it, but setting object variables to Nothing does
free up the memory they occupy. This is especially
important if the recordset variable will be reused or the
procedure has a substantial amount of code after the
recordset is no longer needed.
 
Back
Top