Operation is not allowed when the object is closed.

[leok31]

I keep on getting this error. I cant figure out why!? the weird part is that I have exactly the same stored procedure but insted of FinancialYear it has a calendar year. So instead of of having something like '2013', it has 'FY2012 - 2013'. The calendar year SP works perfectly, but this does not.
Any help.

Option Compare Database
Dim objConn As ADODB.Connection
'Use this code to run the SP and extract all the records

Public Sub LoadVRData()
Dim rsData As ADODB.Recordset
Dim sConnectSQL As String 'to create connection to SQL Server
Dim objCommand As ADODB.Command 'for INSERT results of SP into Access table
Dim i As Integer
Dim strSQL As String

'Creating the connection string to SQL server

sConnectSQL = "Provider=SQLOLEDB;Data Source=MYSERVER; " & _
"Initial Catalog=DisaggregatedPatronage;Integrated Security=SSPI"


Set objCommand = New Command
objCommand.ActiveConnection = sConnectSQL

'Creating the Connection object and Recordset object
Set objConn = New ADODB.Connection
Set rsData = New ADODB.Recordset

objCommand.CommandText = "VRSelection_Screen_FinancialYear"
objCommand.CommandType = adCmdStoredProc
'objCommand.Parameters.Refresh


objCommand(1) = "1.2"
objCommand(2) = "0.02"
objCommand(3) = "30"
objCommand(4) = Forms!vr_selection.filter_year 'Year
'objCommand(4) = "FY2012 - 2013"
objCommand(5) = "weekday"

objCommand.CommandTimeout = 0
objConn.CommandTimeout = 0
Set rsData = objCommand.Execute
'rsData.Open , objConn, adOpenStatic
Do Until rsData.EOF

strSQL = "INSERT INTO VRSelectionDataFY (FinancialYear,timeband,strata,station_entrance_id, station_entrance, " & _
" transactions,ObservedVr,observedSampleSize, avg_val_rate, n, conf_int_val_rate) VALUES " & _
"('" & rsData!FinancialYear & "', '" & rsData!TimeBand & "', " & _
" " & rsData!strata & ", " & rsData!station_entrance_id & ", '" & rsData!station_entrance & "', " & _
" " & rsData!Transactions & ", " & rsData!ObservedVR & ", " & rsData!observedSampleSize & ", " & _
" " & rsData!avg_val_rate & ", " & rsData!n & ", " & rsData!conf_int_val_rate & ")"

CurrentDb.Execute strSQL
rsData.MoveNext
Loop
End Sub

 

[Galaxiom]

You have not opened the connection.

 

[leok31]

I though about that but that code works for another identical SP, except for one column.
I finally fixed it by adding SET NOCOUNT ON in my SP.
I have no idea why it worked. If anyone could please explain why this addition makes a difference to VBA that would be great.
I've seen this solution in other forums and it worked for me a few times. But I also have SP which dont have that addition yet they still work.