Microsoft KB Archive/199708

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

Article ID: 199708

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q199708



Advanced: Requires expert coding, interoperability, and multiuser skills.



SUMMARY
This article shows you how to use ODBCDirect to pass parameters to and return parameters from a SQL Server stored procedure.



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.   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

  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. 

Additional query words: inf

Keywords: kbhowto KB199708

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.