How to know when a Connection or Recordset is open

[Manuelauch]

When I am running a record set and it crashes for any reason, I want to know
how I can tell in my error catcher that either or both the Connection and the
Connection are open so I can close and set them to nothing before I resume
processing

 

[Jack Leach]

From reading posts in the past, one way to do this is to just close it and
trap the error (or bypass)

On Error Resume Next
rs.Close
....
....


A method I've used in the past is to flag the open...

Public Sub SomeName()
Dim rs As DAO.Recordset
Dim brsOpen As Boolean

Set rs = Currentdb.Openrecordset("somename")
brsOpen = True
....
....
....
rs.Close
brsOpen = False
Set rs = Nothing

Exit_Proc:
If brsOpen Then rs.Close
Set rs = Nothing
Exit Sub
Error_Proc:
'error handling here
Resume Exit_Proc
End Sub



Not sure about ADO, never used it. For DAO at least, there's no good way to
close the recordset. I suppose you might be able to write a function that
will handle this closing (already opened or not) and use that instead of the
usual close method.... something like: CloseRecSet(rs)

Public Function CloseRecSet(ByRef rs As DAO.Recordset)
On Error Resume Next
rs.Close
Err.Clear
End Function

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Dirk Goldgar]

When I am running a record set and it crashes for any reason, I want to
know
how I can tell in my error catcher that either or both the Connection and
the
Connection are open so I can close and set them to nothing before I resume
processing

I usually make it a habit to set them to Nothing just after I close them in
the body of the procedure, so in my procedure termination code I can have
this:

On Error Resume Next

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

The "On Error Resume Next" ensures that, even if I'm mistaken and the object
is already closed -- but not set to Nothing -- I don't raise an error.