[RESOLVED] Operation is not allowed when the object is open.

[riechan]

Hi guys. Can anybody help me out here? I keep getting the above error when trying to open a recordset like so:

Set connExcel = CreateObject("ADODB.Connection")
    connExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & Environment("API_Table") & ";" & _
                    "Extended Properties=""Excel 8.0;IMEX=1;" & strHeader & """"
 
Set rsInputData = CreateObject("ADODB.Recordset")
    rsInputData.Open "SELECT * FROM [" & strTblName & "$] WHERE Test_Case_ID ='" & strTestCaseID & "' AND " & _
                    "Action='" & Environment("Action") & "'", connExcel, adOpenStatic
 
Set connAccess = CreateObject("ADODB.Connection")
Set rsObjects = CreateObject("ADODB.Recordset")
    connAccess.Provider = "Microsoft.JET.OLEDB.4.0"
    connAccess.Open "C:\BFTC\Object Config\BFTC_ObjConf.mdb"
 
cntFields = rsInputData.Fields.Count
 
For ctrFields = 0 to cntFields - 1
    If InStr(1, rsInputData.Fields(ctrFields).Value, "*") = 0 Then
        curField = rsInputData.Fields(ctrFields).Name
        curItem = rsInputData.Fields(curField).Value
 
        rsObjects.Open "SELECT * FROM " & strTblName & " WHERE FieldName='" & curField & "'", connAccess
        If rsObjects.RecordCount > 0 Then
        ...
        End If
        rsObjects.Close
    End If
Next    
 
rsInputData.Close
connAccess.Close
connExcel.Close
 
If rsObjects.State <> 0 Then
    rsObjects.Close
End If
 
Set rsObjects = Nothing
Set rsInputData = Nothing
Set connAccess = Nothing
Set connExcel = Nothing

The error occurs when I'm trying to open the second recordset, rsObjects. I was wondering if this error is caused by the first connection and recordset objects (connExcel/rsInputData) still being opened when the second recordset is opened.

If I may also ask, if I close the rsInputData, the data from that query would be lost, right?

[seenu_1st]

hav u tried with early bind?

[riechan]

Sorry. Already fixed the problem. Admin, please delete the thread as needed. Thread resolved.

[seenu_1st]

i tried this late bind method

Const adUseClient = 3
Const adLockOptimistic = 3

Private Sub Form_Load()
Dim cnExcel As Object, cnAccess As Object
Dim rsExcel As Object, rsAccess As Object

Set cnExcel = CreateObject("ADODB.Connection")
Set cnAccess = CreateObject("ADODB.Connection")

Set rsExcel = CreateObject("ADODB.Recordset")
Set rsAccess = CreateObject("ADODB.Recordset")

With cnExcel
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties").Value = "Excel 8.0"
    .Open App.Path & "\sample.xls"
End With

With cnAccess
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open App.Path & "\db1.mdb"
End With

With rsExcel
    .ActiveConnection = cnExcel
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "select * from [sample$]"
End With

With rsAccess
    .ActiveConnection = cnAccess
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "select * from login"
End With

MsgBox rsExcel.RecordCount & "," & rsAccess.RecordCount
End Sub