VBA SQL Run-time Error 3704

[Andy2016]

Hello all,

Using VBA I am calling SQL procedures. Now I am not very experienced with SQL, so please forgive my ignorance. For a certain set of procedures I get the error "Run-time error '3704' Operation is not allowed when the object is closed"

I have tried to work this out myself. I think this error is because the SQL procedure I am calling generates temp tables as part of its operation. However the procedures are encrypted so I can't check the code. From my research I think I need to somehow add 'SET NOCOUNT ON'. However I can't see to do it. I would really appreciate some help. Please see my code below:

Sub ConnectSqlServer()


    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
 
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=HUTCHINSON10\ANDREWSQL2008;" & _
                  "Initial Catalog=TradarBE_Test;" & _
                  "Integrated Security=SSPI;"


    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open the connection and execute.
    conn.Open sConnString
    Dim strSQL As String
    strSQL = "Nav @date='2016-09-07',@fundIdList='1',@accountfilter=1000,@groupbyclr=0,@groupbystrat=0,@groupUnsettledFxRepo=1,@groupbyfund=1,@groupByParentFund=0,@clrIdList=NULL,@stratIdList=NULL,@traders=NULL;"
    Set rs = conn.Execute(strSQL)
    
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("A1").CopyFromRecordset rs
     'Close the recordset
        rs.Close
    Else
        'MsgBox "Error: No records returned.", vbCritical
    End If


    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    
End Sub

 

[Andy2016]

Please ignore I literally just solved this, with a bit of a fluke!