getting recordset via ado/sproc

Trying to get output from sqlserver stored procedure into access recordset via ado.

I have stored procedure with select statement that returns rows when executed from query analyser:

CREATE procedure ps_tblMackVendors_Select_test
AS
set quoted_identifier on
declare @.strsql nvarchar(300)
set @.strsql = 'Select * from tblMackVendors
exec (@.strsql)
GO

When I execute following bit of code from Access, the set rst = cmd.execute runs ok, but the rst.movefirst gets error 3704, Operation is not allowed when the object is closed.

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = mcnnSQLDB
cmd.CommandText = "ps_tblMackVendors_Select_test"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute
rst.MoveFirst

I have tested the stored procedure & the connection using an output parameter, doing a cmd.execute (without the rst..) & the value is retrieved by access okprobably you need to rethink your app structure before you go too far with it. i bet your rst will successfully movefirst if you just do a straight select from your sp.|||My question would be why do you have

CREATE procedure ps_tblMackVendors_Select_test
AS
set quoted_identifier on
declare @.strsql nvarchar(300)
set @.strsql = 'Select * from tblMackVendors
exec (@.strsql)
GO

instead of,...

CREATE procedure ps_tblMackVendors_Select_test
AS
Select * from tblMackVendors
GO

???|||well, i understand he's just experimenting, getting ready for the real stuff, but i'm saying "don't go that route, there is a sign there - no outlet!"|||What happens if you open a recordset using a sql string instead of a stored procedure ? Also, you should debug your vb code and view the locals to see what is going on with the rst object.|||my sp uses string for qry because I want to build the sql from values passed to the sp in the final version.
I did just try it without the string for the sql & it worked - good idea. But it does work with the string when I call the sp from query analyzer. Why do you think there is a difference ?|||multiple recordsets is one option,...

using sp_executesql instead of exec might help.

personally I'd really avoid this method of doing things though... if you are going to use dynamic sql then use it in the application, not in a stored proc.