Determining if recordsets need to be closed

[Amy Blankenship]

I have cleanup code at the bottom of my code that runs on error and also
once the code finishes. This makes sure any open objects, such as
recordsets and querydefs, get properly destroyed under any circumstances.
The problem is that sometimes the code errors before something is open, or
the conditions in the code that determine if the object is initialized
aren't met. In that case, trying to close an open object results in an
error. I haven't been able to find a clean way to determine if an object
has been instantiated, as IsNull and IsEmpty don't seem to work. So I've
resorted to a really lame system of adding a boolean variable for each
object that could need to be closed that determines if it was opened. What
is the best way to handle this?

Thanks;

Amy

 

[John Spencer]

Amy,

When I am in this situation, I usually cheat. I just set my code to ignore
errors with OnError Resume Next for the short segment of code where I am
cleaing up.

NameOfSub_EXIT:

OnError Resume Next
rsAny.Close
Set dbAny = Nothing
Exit Sub

NameOfSub_ERROR:
'Error handling code
End Sub
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[n00b]

Instead of using the "Close" command of the recordset object, just set
the object variable equal to Nothing.

Such as:

Dim rs as Recordset

On Error Goto MyErrorHandler

Set rs = CurrentDb.OpenRecordset('MyTable")

With rs

' Do Stuff, error occurs

End With

rs.Close

Exit Sub

MyErrorHandler:

Set rs = Nothing


Technically, if your error handler is at the end of the procedure and
the procedure ends after the error, the rs variable would go out of
scope and get destroyed anyways, but setting equal to nothing will
make sure it gets destroyed immediately.

 

[Amy Blankenship]

Amy,

When I am in this situation, I usually cheat. I just set my code to
ignore errors with OnError Resume Next for the short segment of code where
I am cleaing up.

I guess that's a cleaner hack than what I am using ;-)

Thanks;

Amy

 

[Curis]

Here is what I do in all of my forms. May not be the best method, but it has
always worked marvelously where implemented. Obviously, the code will need
adjustments as needed in your application.

Sub/Function MyProc()
On Error Goto Err_Handler

[Insert Code Here]

Exit_Handler:
' depending on the object variable, the .close will not work,
' so be mindful of its use here.
If Not rs Is Nothing Then
rs.close
Set rs = Nothing
End If
Exit Sub/Function

Err_Handler:
Msgbox Err.Number & " - " & Err.Description
Resume Exit_Handler

End Sub/Function