How To Get a Return code from a SQL Stored Proc

[joebickley]

Hi I have a stored procedure that i send some params to and it does a whole load of processesing. Within that it has some Return commands to stop the process dead and return a number. If i used the SQL query analyser i can see this on the screen but how can i pick it up when calling it from access VBA?

Any help would be appreciated

Thanks
Joe

 

[vbajock]

This a frag is more a less from a book whose name I have forgotten. It might be "Access 2000 Client/Server"

Function ExecuteSProc(ByVal procnam As String) As Integer


Dim adocommand As New ADODB.Command
Dim adoparam As ADODB.Parameter

On Error GoTo err_h

With adocommand

Set adoparam = .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, , Null)

.Parameters.Append adoparam
.CommandText = procnam
.CommandType = adCmdStoredProc
.ActiveConnection = SqlServer
.Execute

End With

ExecuteSProc = adocommand("RETURN_VALUE&quot

Exit Function

err_h:
MsgBox Error$


End Function