Check if DAO recordset is open

[Paul]

How do I check if a DAO recordset is open in Access 2K?

 

[Ken Snell]

My thought is to check a propery of the recordset and see if you get an
error that indicates that the recordset isn't available. For example,

On Error Resume Next
Dim blnTest As Boolean
blnTest = RecordsetName.EOF
If Err.Number <> 0 Then
' recordset isn't open or assigned == likely error 3420
End If


You also could loop through the Recordsets collection to see if the
recordset is there, for example checking for its name (which may not be
unique, as it's the table name or query name or SQL string of each open
recordset).

 

[Allen Browne]

This example shows how to loop through all the open recordsets in the open
databases to get a count:

Function CountOpenRecordsets() As Long
Dim ws As DAO.Workspace
Dim lngKt As Long
Dim i As Integer

Set ws = DBEngine(0)
For i = 0 To ws.Databases.Count - 1
lngKt = lngKt + ws(i).Recordsets.Count
Next

CountOpenRecordsets = lngKt
Set ws = Nothing
End Function