Return Values from SQL Server to MS Access

[jtertin]

Currently, I am using the following code to execute a stored
procedure. How can I support the use of RETURN values from a stored
procedure to use as a basis for user feedback? Do I just send another
parameter?


Public Sub ExecuteMyProcedure(Par1 As String, Par2 As Double, Par3 As
String)
' Variables
Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim rs1 As ADODB.Recordset

' Establish connection.
Set Conn1 = New ADODB.Connection
Conn1.Open "DSN=" + stDSNName + ";UID=sa;PWD=" + stDatabasePassword
+ ";"

' Open recordset.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "StoredProcName"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = Par1
Cmd1.Parameters(2).Value = Par2
Cmd1.Parameters(3).Value = Par3
Set rs1 = Cmd1.Execute()

' Delete Objects
Set rs1 = Nothing
Set Cmd1 = Nothing
Set Conn1 = Nothing
End Sub

 

[Paul Shapiro]

You should be able to find this with a google search, but I think the
default return value is .Parameters(0), and must be a SQL int and a VBA Long
Integer. You can also use output parameters in a stored procedure if you
need to return something other than an int.