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
set quoted_identifier on
declare @strsql nvarchar(300)
set @strsql = 'Select * from tblMackVendors’
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
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 ok
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 ?