Recordset Closed After Stored Procedure Execution

Question

I'm executing a stored procedure using ADO in VBA. I'm trying to populate the recordset with the results from a stored procedure in SQL Server 2008. Example of the VBA below:

Public Function DoSomething() As Variant()

Dim oDB As ADODB.Connection: Set oDB = New ADODB.Connection
Dim oCM As ADODB.Command: Set oCM = New ADODB.Command
Dim oRS As ADODB.Recordset

oDB.Open gcConn

With oCM
    .ActiveConnection = oDB
    .CommandType = adCmdStoredProc
    .CommandText = "spTestSomething"
    .NamedParameters = True
    .Parameters.Append .CreateParameter("@Param1", adInteger, adParamInput, , 1)
    Set oRS = .Execute
End With

If Not oRS.BOF And Not oRS.EOF Then 'Error thrown here'
    DoSomething = oRS.GetRows()
Else
    Erase DoSomething
End If

oRS.Close
Set oRS = Nothing
oDB.Close
Set oDB = Nothing

End Function

I am receiving the error Operation is not allowed when the object is closed on the line If Not oRS.BOF... which indicates to me that the stored procedure is not returning a result.

However if I execute the stored procedure in SSMS, it returns a single row. The SP goes along the lines of:

CREATE PROC spTestSomething
    @Param1 int
AS
BEGIN

    DECLARE @TempStore table(id int, col1 int);

    INSERT INTO table1
        (param1)
        OUTPUT inserted.id, inserted.col1
        INTO @TempStore
    VALUES
        (@Param1);

    EXEC spOtherSP;

    SELECT
        id,
        col1
    FROM
        @TempStore;
END
GO

The result of executing the procedure in SSMS is:

id    col1
__    ____
1     1

Could anyone help with why the recordset is being closed / not filled?

Answer 1

Based on similar question: “Operation is not allowed when the object is closed” when executing stored procedure i recommended in comment:

I suspect 2 reasons: 1) your sp does not contains: SET NOCOUNT ON; and 2) you're working on variable of type: table.

The most common reason of Operation is not allowed when the object is closed is that that stored procedure does not contain SET NOCOUNT ON command, which prevent extra result sets from interfering with SELECT statements.

For further information, please see: SET NOCOUNT (Transact-SQL)