Building ADODB recordset from Excel worksheet. Error. 1

[vladk]

The error is "Operation is not allowed when the object is closed.

This error occures ONLY when I run code right after I opened my VB6 project. The code runs just fine when I repeat runs during the same session.... I have no clue...

This is the code:

    Dim objRcrdstFromExcel As New ADODB.Recordset
    Dim objExcelConn As New ADODB.Connection
    Dim objCmdExcel As New ADODB.Command
    
    Dim objCmdInsertIntoAccess As New ADODB.Command
    
    Dim strFieldsValues As String
    Dim strFieldsNames As String
    Dim strSQL As String

    Dim strClause As String
       
    ' *****************************************************************************
    With objExcelConn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source= " & gobjBufferWBook.FullName & ";Extended Properties=Excel 8.0;"
        .Open
    End With

    strClause = GetBusItemsClause
    ' *****************************************************************************

    Set objCmdExcel.ActiveConnection = objExcelConn
    
    objCmdExcel.CommandType = adCmdText
    objCmdExcel.CommandText = "SELECT * FROM [" & SHEET_REQUESTS & "]" & vbCrLf & "WHERE " & strClause
    objRcrdstFromExcel.CursorLocation = adUseClient
    objRcrdstFromExcel.CursorType = adOpenStatic
    objRcrdstFromExcel.LockType = adLockReadOnly
    
    objRcrdstFromExcel.Open objCmdExcel ' <<<<<<<<<< This is the line that gives the error...

    ......

If anybody could give me an advice, I would highly appretiate it.

:0)

 

[SkipVought]

Hi,

Where do you set the gobjBufferWBook workbook object in the initial process?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

same for objExcelConn

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vladk]

Hi Skip,

I set gobjBufferWBook in this procedure:

Public Sub OpenBufferBook()
    '******************************************************************************
    '
    '   DESCRIPTION:
    '
    '   CHANGE LOG:
    '
    '   DATE               PROGRAMMER   COMMENTS
    '   ----------              ----------                 --------------------------------
    '   07/22/2010        V Kloyzner          New method
    '
    '******************************************************************************
    Const METHOD_NAME As String = "OpenBufferBook"
    
    On Error GoTo MethodExit

    Dim objFSO As New Scripting.FileSystemObject
    
    Set gobjBufferWBook = gobjExcel.Workbooks.Open(objFSO.BuildPath(App.Path, "Workbooks\Buffer.xls"), False, False)

MethodExit:

    If Err.Number <> 0 Then
        MsgBox "Error " & CStr(Err.Number) & " in " & METHOD_NAME & vbCr & Err.Description
    End If
        
End Sub

I call this procedure before I call the code in question.
All the info about objExcelConn is in the initial code I showed. Please feel free ask me more.

 

[SkipVought]

And the CONNECTION?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vladk]

Skip,

I am not sure if I understand you. All connection code I have already showed:

    Dim objRcrdstFromExcel As New ADODB.Recordset
    Dim objExcelConn As New ADODB.Connection
    Dim objCmdExcel As New ADODB.Command
......
    ' *****************************************************************************
    With objExcelConn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source= " & gobjBufferWBook.FullName & ";Extended Properties=Excel 8.0;"
        .Open
    End With

    strClause = GetBusItemsClause
    ' *****************************************************************************

    Set objCmdExcel.ActiveConnection = objExcelConn

 

[SkipVought]

BTW, the workbook does not need to be OPEN, in order to open a connection and query the tables therein.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vladk]

OK...

But what about the error? What right now I am doing to overcome it, is this:

    On Error Resume Next
        objCmdExcel.Execute
    On Error GoTo MethodExit
    
    objRcrdstFromExcel.Open objCmdExcel

It lets me run the code (I tried this approach about 10 minutes ago), but I would like to do it in right way.

:0)

 

[vladk]

I open the book to copy data in that book from some other place.

 

[SkipVought]

PLEASE answer the formerly posted question regarding objExcelConn!!!

How is this object set in the initial process?

If you have a question regarding objCmdExcel, or ANY OTHER OBJECT, please include COMPLETE information regarding that object. How the object is SET would be a nice start IN EACH CASE, and where in the process.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vladk]

Skip,

I do not SET objExcelConn and objCmdExcel explicitely. I assume, this was sufficient:

Dim objExcelConn As New ADODB.Connection
Dim objCmdExcel As New ADODB.Command

I am not sure if I correctly answered your question.

 

[vladk]

Skip, to test this: "BTW, the workbook does not need to be OPEN, in order to open a connection and query the tables therein.",

I added these two lines:

gobjBufferWBook.Save
gobjBufferWBook.Close

before this code:

    With objExcelConn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source= " & gobjBufferWBook.FullName & ";Extended Properties=Excel 8.0;"
        .Open
    End With

... and got the error:

Operation is not allowed when the object is closed

 

[SkipVought]

Here's some code that I have used...

    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, here's a function that works. The called workbook is NOT opened!

Function GetNbrRes(sResource As String)
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    Dim sPrevCC As String, BCC As Boolean
    
    sPath = "C:\_Databases\_Excel"
    sDB = "ResourceData"
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT A.ResCC "
    sSQL = sSQL & "FROM [ResourceData$] A "
    sSQL = sSQL & "WHERE Left(A.ResCC,1)='5' "
    sSQL = sSQL & "  AND A.Resource Like '%" & sResource & "%' "
    sSQL = sSQL & "Order By A.ResCC "
        
    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

        .MoveFirst
        sPrevCC = ""
        BCC = False
        Do Until (.EOF)
            If sPrevCC = rst(0) Then
                If Not BCC Then
                    BCC = True
                    GetNbrRes = GetNbrRes - 1
                End If
            Else
                BCC = False
            End If
            GetNbrRes = GetNbrRes + 1
            sPrevCC = rst(0)
            .MoveNext
        Loop
        .Close
    End With
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vladk]

Skip,

Your connection generates exactly the same error as mine. In addition, I noticed, then when I run it second time in the same session and when it works (as mine too), the data types of the EXCEL fields are now different from the types with my way of connection...

Don't know what to do...

Thank you, Skip, it's OK. I have my way around...

 

[vladk]

Skip, yes, I indeed can keep the book closed!
The initial error is still a problem, but it is OK for now.

Thank you again, Skip!

Vlad