Return Values from SQL Server to MS Access



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

Public Sub ExecuteMyProcedure(Par1 As String, Par2 As Double, Par3 As
' 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(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.

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