ADO recordset does not open after executing stored procedure (1 Viewer)

[valeryk2000]

In my Access front end VBA code opens ADO recordset from stored procedure on remote SQL Server 2008. It worked OK for 2 years and SUDDENLY it stopped working. The code execution stops at line (see below) with popup:
Run Time errror '3704' Operation is not allowed when the object is closed
On the SQL Server side the procedure runs without problems

Call ConnectToDB <<=== connects to SQL Server database
Call SetCommand <<=== sets ADO command 'cmd'
Call SetRecordset(rsDb)
cmd.ActiveConnection = connSql
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_rCasesEnteredSummary"
cmd.Parameters.Append cmd.CreateParameter("@ReturnValue", adInteger, adParamReturnValue, 3)
cmd.Parameters.Append cmd.CreateParameter("@HospitalID", adVarWChar, adParamInput, 50, strHospital)
cmd.Parameters.Append cmd.CreateParameter("@EventSrartYear", adInteger, adParamInput, , lngEventStartYear)
cmd.Parameters.Append cmd.CreateParameter("@EventStartMonth", adInteger, adParamInput, , intEventStartMonth)
cmd.Parameters.Append cmd.CreateParameter("@lngEventEndYear", adInteger, adParamInput, , lngEventEndYear)
cmd.Parameters.Append cmd.CreateParameter("@intEventEndMonth", adInteger, adParamInput, , intEventEndMonth)
cmd.Execute
intRetVal = cmd.Parameters(0).Value
rsDb.Open cmd
If rsDb.EOF Then <<=== line the code stopped
MsgBox "There is no data for this hospital"
rsDb.Close
Exit Sub
Else

Any idea WHY the recordset does not open anymore?
Here is the proc (a little bit sloppy but simple)

ALTER Procedure [dbo].[sp_rCasesEnteredSummary]
@HospID  nvarchar(50),
@sYear  integer,
@sMonth  integer,
@eYear  integer,
@eMonth  integer
As
declare
@startDate  nvarchar(12),
@endDate  nvarchar(12)
set @startDate=CAST(@sMonth as nvarchar )+'/1/'+CAST(@sYear as nvarchar)
set @endDate=CAST((@eMonth ) as nvarchar )+'/1/'+CAST(@eYear as nvarchar)
print @startdate
print @enddate
SELECT YEAR(EventDate) yr, MONTH(EventDate) MonthNum,
DATENAME(month,EventDate) MonthName,
 DATENAME(month,EventDate) + ', '+ cast(YEAR(EventDate) as CHAR(4)) MonthYear ,
case RecommendCaseReview
When 1 then 'Recommended Case Review'
When 0 then 'Did Not Recommend Case Review'
Else 'Pt Safety Spec has not yet edited case in Clinical Review'
end AS RecCaseRev, 
COUNT(*) as cnt,
h.HospitalName + ' Hospital' HospitalName
FROM tblClinicalReview t Inner Join lstHospital h
on t.HospitalID=h.HospitalID 
WHERE t.EventDate Is Not Null
And t.HospitalID=@HospID
And (EventDate > CAST(@startDate as date) 
 AND EventDate<=CAST(@endDate as date))
Group By 
YEAR(EventDate), MONTH(EventDate),DATENAME(month,EventDate),
 DATENAME(month,EventDate)+ ', '+ Cast(YEAR(EventDate) as CHAR(4)),
RecommendCaseReview,h.HospitalName
Order By 1, 2, 3

 

[mdlueck]

I am used to seeing an adoRS object returned on the adoCMD.Execute LOC.

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()
    .CommandText = "clsObjMEToolingTbl_LocateMEToolingByID"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@id").Value = Me.id

    Set adoRS = .Execute()
  End With

  With adoRS
'Do something with the output of the SP...
    'Close the database table
    .Close
  End With

So I have no idea how the adoCMD object would have even known which adoRS object you were meaning to receive the result set.

 

[valeryk2000]

You can do it either way.
I prefer
adoCMD.Execute
adoRs.Open adoCMD
because it allows ANY type of recordset, while Set adoRS = ... forward only