ADODB connection error

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

ADODB connection error

Post by jstevens »

I'm having a challenge with the ADODB connection producing an error message: Operation is not allowed when the object is closed.

If I run the code it returns the recordset without any challenges. Subsequent to this bit of code I run another routine (does not include a recordset) to update a database table with a different connection string and then come back to run this code again. It is the second time that I get the error message.

Code: Select all

    Dim CnBudget As ADODB.Connection
    Dim rsBudget As ADODB.Recordset
    
    Set rsBudget = New ADODB.Recordset

    DeclareConnectionVariables

    Set CnBudget = New ADODB.Connection
    CnBudget.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    CnBudget.CommandTimeout = 0
    
    Application.Run "SQL_Build_Sequence_Routine"
        CnBudget.Execute SQLStrSequence
        
    Application.Run "SQL_Build_String"
        CnBudget.Execute SQLStr    
        
    rsBudget.Open SQLStr, CnBudget, adOpenStatic

    Cells(7, 2).CopyFromRecordset rsBudget     'Here is where the error message appears

    rsBudget.Close
    CnBudget.Close
    Set rsBudget = Nothing
    Set CnBudget = Nothing
Your thoughts are appreciated.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ADODB connection error

Post by HansV »

Since you're calling other procedures whose code you haven't posted, it's impossible to know what exactly the code does, but I'm confused by the two lines

Code: Select all

        CnBudget.Execute SQLStr    
        
    rsBudget.Open SQLStr, CnBudget, adOpenStatic
Both use the same SQL string. I don't understand that.
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: ADODB connection error

Post by jstevens »

Hans,

The CnBudget.Execute SQLStr is running a script generated by a call to Application.Run "SQL_Build_String. SQLStr is a public variable. The line "rsBudget.Open SQLStr, CnBudget, adOpenStatic" returns a recordset and results of that recordset are dumped to Excel.

The other code not posted previously is:

Code: Select all

Sub Update_Budget_Acct_Values()

Dim Cn As ADODB.Connection
        
    DeclareConnectionVariables

    Set Cn = New ADODB.Connection
        Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"

        
    SQLStr = "Builds a SQL on the fly" & Chr(10)
    SQLStr = SQLStr & "Add more script here" & Chr(10)
    SQLStr = SQLStr & "This script works" & Chr(10)

        Cn.Execute SQLStr
                
    'Clear zeros that were just posted
    SQLStr = "DELETE" & Chr(10)
    SQLStr = SQLStr & "FROM budget" & Chr(10)
    SQLStr = SQLStr & "WHERE dept_no ='12345'" & Chr(10)
    SQLStr = SQLStr & "AND scenario = 'budget'" & Chr(10)
    SQLStr = SQLStr & "AND amount = 0"
                        
        Cn.Execute SQLStr
                
			Cn.Close
			Set Cn = Nothing
        
End Sub

Regards,
John

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: ADODB connection error

Post by jstevens »

Hans,

I resolved the error message by adding one line of code to the SQLStr: SET NOCOUNT ON
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: ADODB connection error

Post by HansV »

Congratulations! :thumbup:
Best wishes,
Hans