>> sql output parameters

J

Jonathan

Hi using Access 2003 and sql2005.
I have a stored procedure that returns a recordset and some output
parameters. When I test this in sql I get the recordset and the output
parameters.

The problem is that is Access I can either get the recordset or the output
parameter. How do I get both?

I my example below
Set rs = cmd.Execute ...returns recordset only
cmd.Execute Options:=adExecuteNoRecords ...returns parameters only

My work-around is to use both. But this seems to me rather naf!

*** code snippet start ***
Set cmd.ActiveConnection = conn
cmd.CommandTimeout = 0
cmd.CommandText = "spl_RunQuery"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("@command", adVarChar, adParamInput, 4096,
sqlcmd)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@err", adInteger, adParamOutput)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@err_msg", adVarChar, adParamOutput, 2048)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@count", adInteger, adParamOutput)
cmd.Parameters.Append prm

'Get the recordset.
Set rs = cmd.Execute
'Get the output values.
cmd.Execute Options:=adExecuteNoRecords

Dim error_code As Long
error_code = cmd.Parameters("@err")
error_message = cmd.Parameters("@err_msg")
RecordCount = cmd.Parameters("@count")

*** code snippet end ***

Any ideas or recommendations appreciated :)

Many thanks,
Jonathan
 
J

Jonathan

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top