ACC2000: How to Use ADO to Create a Parameterized Jet Stored Procedure

MORE INFORMATION

The first example, CreateJetStoredProcedure, creates a database containing sample tables, and then in that database, creates a Microsoft Jet stored procedure named FindCustomersProc. This stored procedure requires a parameter named prmCity.

The second example, RSFromParameterQuery, runs FindCustomersProc with a city name Paris as the parameter. The stored procedure, FindCustomersProc, queries the Customers table to find any records with the matching city name. The results are displayed in the Immediate window.

To see how these examples work, follow these steps:

1. In a new Microsoft Access 2000 database, create a new module.
2. On the Tools menu, click References, and then create a reference to Microsoft ADO Ext. 2.5 for DDL and Security.
3. Type the following two functions in the new module:

   Function CreateJetStoredProcedure()
   
      Dim ADOConnection As New ADODB.Connection
      Dim ADOCommand As New ADODB.Command
      Dim ADOXCatalog As New ADOX.Catalog
   
      On Error GoTo ErrorHandler
   
      ' Delete the database JetStoredProcedure.mdb if it already exists.
      Kill "c:\JetStoredProcedure.mdb"
   
      ' Create a new database called JetStoredProcedure.mdb
      ADOXCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data " _
      & "Source=c:\JetStoredProcedure.mdb"
   
      ' Set the Connection Properties and open the connection.
      With ADOConnection
              .Provider = "Microsoft.Jet.OLEDB.4.0"
              .Open "Data Source=c:\JetStoredProcedure.mdb"
              'Create a new table
              .Execute _
              "Create Table Customers(CustID integer,City text(50))"
   
              ' Insert 2 new records into the table.
              .Execute _
              "insert into Customers(CustID, City) Values (1,'London')"
   
              .Execute _
              "insert into Customers(CustID, City) Values (2,'Paris')"
       End With
   
      ' Set the Command Properties.
      With ADOCommand
           .CommandText = "PARAMETERS `prmCity` Text;Select * " _
           & "From Customers where City = `prmCity`"
      End With
   
      ' Open the Catalog
      Set ADOXCatalog.ActiveConnection = ADOConnection
   
      ' Append the new Stored Procedure
      ADOXCatalog.Procedures.Append "FindCustomersProc", ADOCommand
   
      ' Clean up
      ADOConnection.Close
      Set ADOCommand = Nothing
   
      Exit Function
   ErrorHandler:
   
          If Err = 53 Then
              Resume Next
          End If
   
      MsgBox Error & " Error# " & Err
      Exit Function
   End Function
   
   Function RSFromParameterQuery(strCity As String)
   
      On Error GoTo MyErrorHandler2
   
      Dim ADOParameter As ADODB.Parameter
      Dim ADOCommand As New ADODB.Command
      Dim ADORST As New ADODB.Recordset
      Dim ADOConnection As New ADODB.Connection
   
      ' Set Connection Properties and open Connection
      With ADOConnection
           .Provider = "Microsoft.Jet.OLEDB.4.0"
           .Open "Data Source=c:\JetStoredProcedure.mdb"
      End With     
   
      ' Create a new parameter object and set it's value.
      Set ADOParameter = ADOCommand.CreateParameter("prmCity", _
      adVarChar, adParamInput, Len(strCity))
      ADOParameter.Value = strCity
   
      ' Set Command properties and append parameter
      With ADOCommand
           .ActiveConnection = ADOConnection
           .CommandText = "FindCustomersProc"
           .CommandType = adCmdStoredProc
           .Parameters.Append ADOParameter
      End With
   
      ' Open Recordset against the Stored Procedure
      ADORST.Open ADOCommand
   
      ' List all matching records.
      Do Until ADORST.EOF
          Debug.Print ADORST(0), ADORST(1)
          ADORST.MoveNext
      Loop
   
      ' Close open objects before quiting.
      ADORST.Close
      ADOConnection.Close
      Set ADOCommand = Nothing
      Set ADOParameter = Nothing
   
      Exit Function
   
   MyErrorHandler2:
      MsgBox Error & " error # " & Err
   
   End Function

4. Run the CreateJetStoredProcedure function in the Immediate window by typing the following:

   CreateJetStoredProcedure

5. Run the RSFromParameterQuery function in the Immediate window by typing the following:

   RSFromParameterQuery("Paris")

Note that the following results are returned in the Immediate window:

2

Paris