Closing recordsets

[tkelley via AccessMonster.com]

I'm really trying to establish good habits regarding garbage collection while
coding in VBA.

I like to always do a "rst.close" and "set rst=nothing". However, sometimes
"rst.close" fires an error because there was an error while it was in scope
(I assume, anyway).


So, I haven't found a definitive answer on whether "set rst=nothing" alone is
good enough, even if rst is still in scope and open. Does "set rst=nothing"
do a close by definition?

Any opinions from the MVP's / uber-gurus?

 

[Rob Wills]

Looks like you're entering the realms of error trapping.....

Before you start closing your objects type this line

on error resume next

as you don't care if your objects can't close if they're not open

HTH
Rob

 

[MikeJohnB]

If your going to do things in a manner that sets everything correctly, then
the On Error Resume Next should be followed by On Error Goto 0 after the line
or lines of code on which the error is to be ignored.

On Error Resume Next 'The next line of code which is to be ignored for Errors

The Code that may throw and error

On Error Got 0 'Resets Error Trapping

The rest of your code which will be error checked again

Regards Mike B

 

[DaveT]

Sub/Function ....

Dim ws As DAO.Workspace
Dim dbs As DAO.Database
Dim r As DAO.Recordset

On Error Goto TrapIT

Set ws ...
Set dbs ...
Set r ...

'stuff using r etc

EnterHere:

On Error Resume Next

If Not r Is Nothing Then
r.Close
Set r = Nothing
End If

Set dbs = Nothing
ws.Close
Set ws = Nothing

Exit Sub/Function

TrapIt:
'Msgbox stuff reporting error to user
Resume EnterHere

 

[Tom Wickerath]

Mike and Rob,

Beware that On Error Resume Next is not guaranteed to work, unless you
distribute your application in the compiled .mde (or .accde for 2007) format.
The reason is that a given user may have set their Visual Basic Editor option
to Break On All Errors. If you distribute in the .mdb (or .accdb) format, and
attempting to close a recordset throws an error, it will not be ignored if a
particular user has a non-default setting for the Error Trapping option. On
the other hand, it is impossible to break into code in the compiled formats,
so it will work there. This option is a global option; it does not "travel"
with your database.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________