Runtime error '3705': opertion is not allowed when the object is open

Question

'Redundancy checking function
Private Function Redundancy_Check(Sup_ID)
rs.Open "Select*from tblSupplier where Supplier_ID='" & Sup_ID & "'", cn, 3, 3
If rs.RecordCount > 0 Then
    If Not (rs.BOF And rs.EOF) Then
        iTerminate = True
    End If
End If
Set rs = Nothing
End Function

Is there something wrong with my coding? I've checked the log and it states:

"Cannot load control usrGrid."

I'm using Visual Basic 6.0 + MS Access.

Answer 1

The run-time error 3705 occurs because only client-side ADO recordsets rs can be disconnected. It occurs when you attempt to disconnect a server-side ADO recordset. You need to set the CursorLocation property of the ADO Recordset to adUseClient. Assume that your ADODB.Connection variable is called cn, you need to add the following line before open the connection :

Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient ' avoid error 3705
cn.Open "..."

---

Here's the complete example provided by Microsoft

Private Sub Command1_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

'Place cn.CursorLocation = adUseClient here
cn.Open "Provider=SQLOLEDB;Data Source=<SQL Server>;Initial Catalog=pubs;User Id=<UID>;Password=<PWD>" 
rs.Open "Select * from authors", cn, adOpenStatic, adLockBatchOptimistic

Set rs.ActiveConnection = Nothing

rs.Close
cn.Close

End Sub

See

• PRB: Run-Time Error Message 3705
• BOF, EOF, and Bookmark Properties Example (VB)

Answer 2

Another potential cause is that rs already has a recordset open. Because your code doesn't Dim and initialize rs inside of your method I'm assuming it's created as a module variable and it doesn't look like it's being closed.

Somewhere you need to call rs.Close before you can call rs.Open again. Add this before your rs.Open line and run your code.

If Not rs.State = adStateClosed Then
    MsgBox "The recordset is already open"
End If