ACC97: How to Use ODBCDirect to Call SQL Server Stored Procedures

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

1. In a Microsoft Access database, create a new module, and then type or paste the following code:

    Option Explicit
    Sub Pass_Param_SP(VarInput1 As Integer, VarInput2 As Integer)
   
            On Error Resume Next
   
            Dim WrkSp As Workspace
            Dim qdf As QueryDef
            Dim cnn As Connection
            Dim strConnect As String, strSQL As String
   
            Set WrkSp = CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
   
            'The connection string assumes the DSN name is SQLMachine, the
            'User ID is sa, and there is no password.
            strConnect = "ODBC;DSN=SQLMachine;DATABASE=Pubs;UID=;PWD=;"
   
            Set cnn=WrkSp.OpenConnection("",dbDriverNoPrompt,False,strConnect)
   
            'Remove the stored procedure NewAdd if it exists.
            strSQL = "DROP PROCEDURE NewAdd;"
   
            cnn.Execute strSQL
   
            On Error GoTo 0
   
            'This procedure simply returns the first variable passed.
            strSQL = "CREATE PROCEDURE NewAdd (@Var1 int, @Var2 int)" & _
            "AS RETURN @Var1;"
   
            cnn.Execute strSQL
   
            'The first question mark represents the value returned.
            'The second question mark represents the first value passed.
            'The third question mark represents the second value passed.
            Set qdf = cnn.CreateQueryDef("qry", "{ ? = call NewAdd(?,?) }")
   
            'The parameters for stored procedures can be an input parameter
            'an output parameter, both, or the return value. The Direction
            'property is used to indicate what type of parameter.
            
             qdf.Parameters(0).Direction = dbParamReturnValue
             qdf.Parameters(1).Direction = dbParamInput
             qdf.Parameters(2).Direction = dbParamInput
          
             qdf.Parameters(1) = VarInput1
             qdf.Parameters(2) = VarInput2
            
             qdf.Execute
   
             Debug.Print "The returned values are " & qdf.Parameters(1).Value & " and " & qdf.Parameters(2).Value
   
             qdf.Close
             cnn.Close
             WrkSp.Close
         End Sub
   
   
   
   					

2. To test this procedure, type the following line in the Debug window, and then press ENTER:

   Pass_Param_SP 25, 100
   					

   Note that both parameters are returned.