Retrieve the return value from a SQL Server stored procedure

[juanfiesta]

I need to use an Access 2007 front end to allow users to initiate processes on a SQL Server 2005 database. In most cases, the initiation will involve passing a parameter or two. The SP(s) will not generate data back but will generate return codes to indicate success, failure, etc. and provide them via the T-SQL Return. How do I set up the call to the SP so that I can retrieve the return code.

TIA,

John

 

[dhookom]

Have you google this? Have you considered a pass-through query?

Duane
Hook'D on Access
MS Access MVP

 

[danvlas]

http://support.microsoft.com/kb/285295

"Classic" way to call a sp:

Dim cmdSP as New ADODB.Command
Dim lngRetVal as Long
With cmdSP
   .ActiveConnection=CurrentProject.Connection
   .CommandText = "YourSpName"
   .CommandType = adStoredProc
   .Parameters.Refresh
   '.Parameters("@SomeInputParam")="SomeValue"
   .Execute
   .lngRetval=.Parameters(0)
   'or lngRetVal = .Parameters("@Return_value")
End With

Good luck!


Daniel Vlas
Systems Consultant

 

[juanfiesta]

Thank you Daniel. That's exactly what I needed.