Testing for recordset opened 3

[MikeCt]

Hi
I would like to know if there is a way to test if a recordset is still open. I would like to place this in the
form.unload procedure to be sure the recordset are closed on exit. This example give an error if the recordset has been previously closed

If rsRs1.RecordCount > 0 Then
rsRs1.Close
Set rsRs1 = Nothing
End If

Thanks
Mike

 

[PatrickIRL]

You can check the state of the recordset ...

if rsRS1.State = adStateOpen then
    rsRS1.close
    Set rsRS1 = nothing
end if

Patrick

 

[MikeCt]

Patrick
I tried your example and I got a error message
"Method or data member not found"

I'm using VB6 and I guess it does not support this method
Mike

 

[HughLerwill]

>This example give an error if the recordset has been previously closed

So just handle the error.

 

[MikeCt]

Sometime the simple answer is the hardest to find
Thanks
Mike

 

[Andrzejek]

PatrickIRL gave you the answer for the ADODB recordset, and his code should work without the error.

What kind of recordset are you useing?
How did you declare your recordset?

Because even if you handle the error "Method or data member not found", you still do not know if your rsRS1 is closed or open.

Have fun.

---- Andy

 

[dpaulson]

I've done it this way


If Not (rsReceipt Is Nothing) Then
rsReceipt.Close
Set rsReceipt = Nothing
End If


David Paulson

 

[Andrzejek]

dpaulson - your code does not detect if the recordset is closed or open.

Reference to Microsoft ActiveX Data Object 2.X Library

Dim Cn As ADODB.Connection
Dim rsRS1 As ADODB.Recordset

Set Cn = New ADODB.Connection

Cn.ConnectionString = "Driver={Microsoft ODBC for Oracle};" & _
        "SERVER=ABCD;"
Cn.CursorLocation = adUseNone
Cn.Open

Set rsRS1 = New ADODB.Recordset

rsRS1.Open "SELECT SYSDATE FROM dual", Cn

Debug.Print rsRS1!SYSDATE.Value[green]
'Shows: 3/5/2010 8:45:46 AM[/green]

rsRS1.Close
[red]
If Not (rsRS1 Is Nothing) Then
    [highlight]rsRS1.Close[/highlight]
    Set rsRS1 = Nothing
End If[/red]

Cn.Close
Set Cn = Nothing

The highlighted line of code gives you an error:

[tt]Run-time error 3704:

Operation is not allowed when the objest is closed.[/tt]

Have fun.

---- Andy

 

[dpaulson]

Yes, I realize that it will throw an error when a recordset is just closed, but in MikeCT post when the recordset is closed, he also sets it to nothing. So checking if the is a recordset is something should mean that it is also open. Maybe the correct way will be to check if there is a recordset and then check to see if it is open to know whether you should close a recordset or just set it to nothing or both.

David Paulson

 

[Andrzejek]

checking if the is a recordset is something should mean that it is also open.

Not true.

The line of code:

[tt]rsRS1.Close
If Not (rsRS1 Is Nothing) Then[/tt]

will always return True (in the code above) no matter if the recordset is open or closed. In my example above I closed the recordset, then checked if [tt]not is nothing[/tt] - this check returned True, and then I crashed on rsRS1.Close because it was already closed.

PatrickIRL's sample of checking the [tt].State[/tt] of the recordset is the way to go (at least for the ADODB recordsets) IMHO

Have fun.

---- Andy