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:
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:
Code:
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