Access 2000 ADO Recordset from Stored Procedure

[Guest]

Using VBA code, I am having a problem getting data returned from a SQL Server
2000 Stored Procedure into a ADO Recordset. My Stored Procedure has 2 INPUT
Parameters and all it does is return all records from our Customers table
based on the parameters entered. ie: SELECT * FROM tblCustomers WHERE CustID
= @CustID

I keep getting the error "Run-time error 3704: Operation is not allowed when
the object is closed." when the "MsgBox rs.Fields(0)" line of code is
executed.

My VBA Code is below:

Dim con As ADODB.Connection
Set con = New ADODB.Connection

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Dim sDBConnect As String
sDBConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Data Source=REPORTING;Initial Catalog=MyTestDB"
con.Open sDBConnect
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "usp_MyStoredProc"

cmd.Parameters.Append cmd.CreateParameter("@CustID", adVarChar,
adParamInput, 10, "GHP")
cmd.Parameters.Append cmd.CreateParameter("@ReportType", adVarChar,
adParamInput, 1, "D")

Set rs = cmd.Execute
MsgBox rs.Fields(0)

Set cmd.ActiveConnection = Nothing

 

[Guest]

Here is my Stored Procedure code that may help. I am inserting records into
a Temp table because I need to display either "Detail" records or "Summary"
records based on the parameter entered.

Is there something with the way my stored procedure is written that is
preventing anything returned via ADO recordsets? The reason I ask is, I
changed my stored procedure to a straight "SELECT * FROM Member FROM Member
WHERE Member.CustID = @CustID" and the ADO recordset worked fine,

CREATE PROCEDURE usp_MyStoredProc
@CustID AS VarChar(10) = NULL,
@ReportType AS VarChar(1) = 'D'
AS
SELECT
Member.CustID,
Member.MemberAutoPK,
Member.CarrierMemID,
MemberPlusID = Member.LastName + ', ' + Member.FirstName + '(' +
Member.CarrierMemID + ')',
MemberLastFirstName = Member.LastName + ', ' + Member.FirstName,
Member.FirstName,
Member.LastName,
Member.DateDispensed
INTO #tmpRecs
FROM Member
WHERE Member.CustID = @CustID

-- Check the Report Type to display Detail records or Summary Records
IF @ReportType = 'D'
BEGIN
SELECT * FROM #tmpRecs ORDER BY CarrierMemID, DateDispensed;
END
ELSE
BEGIN
SELECT 'Report #1' AS Report, COUNT(DISTINCT MemberPlusID) AS NumMembers
FROM #tmpRecs;
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[Guest]

Here is the Stored Procedure that works:

CREATE PROCEDURE usp_z_GetMember

@CustID AS VarChar(10)

AS
SELECT * FROM Member WHERE Member.CustID = @CustID
GO

 

[Guest]

Ok, it is solved thanks to the following MSKB article:
http://support.microsoft.com/default.aspx/kb/235340

My revised VBA module is below:

Dim adoCn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim adoCm As ADODB.Command
Dim strSQL As String

Dim sDBConnect As String
sDBConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Data Source=REPORTING;Initial Catalog=MyTestDB"

Set adoCn = New ADODB.Connection
With adoCn
.ConnectionString = sDBConnect
.CursorLocation = adUseServer
.Open
End With

Set adoCm = New ADODB.Command
With adoCm
Set .ActiveConnection = adoCn
.CommandType = adCmdStoredProc
.CommandText = "usp_MyStoredProc"
.Parameters.Append .CreateParameter("@CustID", adVarChar, adParamInput,
10, "GHP")
'the default for Prepared statements is false.
'.Prepared = False
End With

Set adoRs = New ADODB.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
'Uncomment the next line with the SQLOLEDB provider to fix the error.
.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm, , , , adCmdStoredProc

While Not adoRs.EOF
MsgBox adoRs.Fields(4).Value
adoRs.MoveNext
Wend

adoCn.Close
Set adoCn = Nothing
Set adoRs = Nothing