>> sql output parameters

[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

 

[vanderghast]

Have you tried to read the parameter, from the parameters collection, after
you executed the command? May not work in ADO (but that works in ADO-Net:
http://blogs.msdn.com/spike/archive...n-and-out-parameter-values-using-ado-net.aspx).



Vanderghast, Access MVP

 

[vanderghast]

Have you tried to read the parameter, from the parameters collection,
after you executed the command? May not work in ADO (but that works in
ADO-Net:
http://blogs.msdn.com/spike/archive...n-and-out-parameter-values-using-ado-net.aspx).



Vanderghast, Access MVP

 

[Jonathan]

Have you tried to read the parameter, from the parameters collection, after
you executed the command? May not work in ADO (but that works in ADO-Net:
http://blogs.msdn.com/spike/archive...n-and-out-parameter-values-using-ado-net.aspx).



Vanderghast, Access MVP

Hi Vanderghast, thanks for the link. Unfortunitely no answer was reveiled -
code snippet below includes reading from parameter collection already.

Many thanks,
Jonathan