How to know when a Connection or Recordset is open

  • Thread starter Thread starter Manuelauch
  • Start date Start date
M

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
 
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)
 
Manuelauch said:
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
Click to expand...


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.
 
Back
Top