ACC2000: Creating a Parameterized Jet Stored Procedure with DDL

ID: Q202116


The information in this article applies to:
  • Microsoft Access 2000

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


SUMMARY

This article shows you how to create a stored procedure in the Visual Basic Environment and, if needed, how to add a parameter to the existing stored procedure.

NOTE: Native stored procedures are new in Microsoft Access 2000. You cannot create and run stored procedures through the user interface. You must use the Visual Basic Environment with ActiveX Data Objects (ADO) or the Database Definition Language (DDL).


MORE INFORMATION

The following steps show you how to create a stored procedure with a parameter and how to run it in the Visual Basic Environment:

  1. In the sample database Northwind.mdb, create a new module called TestStoredProc.


  2. On the Tools menu, click References. In the list of available references, click to select (check) Microsoft ActiveX Data Objects 2.1 Library.


  3. Type the following in the new module:


  4. 
    Public Function CreateProc()
    
       Dim strProc As String
    
       strProc = "Create Procedure qryCustByCity " & _
                 "(prmCity varchar) as " & _
                 "select * from Customers where City = prmCity"
    
       CurrentProject.Connection.Execute strProc
    
    End Function 
  5. In the Immediate window, type the following and press ENTER:


  6. 
    ?CreateProc 
Steps 1 though 4 create a stored procedure called strProc, which queries for records whose city matches the parameter, prmCity. The remaining steps involve creating code that runs strProc and passes a parameter.

  1. Return to the module that you created in step 1.


  2. Type the following code:


  3. 
    Public Function RSFromParameterQuery(strCity As String)
    
       Dim prm As ADODB.Parameter
       Dim cmd As ADODB.Command
       Dim rst As ADODB.Recordset
    
       Set cmd = New ADODB.Command
       Set cmd.ActiveConnection = CurrentProject.Connection
    
       cmd.CommandText = "qryCustByCity"
       cmd.CommandType = adCmdStoredProc
    
       Set prm = cmd.CreateParameter("prmCity", adVarChar, _
                 adParamInput, Len(strCity))
    
       prm.Value = strCity
    
       cmd.Parameters.Append prm
    
       Set rst = New ADODB.Recordset
       rst.Open cmd
    
       Do Until rst.EOF
          Debug.Print rst(0), rst(1), rst(2)
          rst.MoveNext
       Loop
    
    End Function 
  4. In the Immediate window, type the following and press ENTER:


  5. 
    ?RSFromParameterQuery("London") 
In the Immediate window, you should see a listing of the customers in London.

Additional query words: inf

Keywords : kbdta AccCon QrySqlvw QryHowto
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: May 13, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.