Microsoft KB Archive/201493

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

Article ID: 201493

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q201493



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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
Support for the definition of stored procedures has been added to Microsoft Jet 4.0. This article provides Visual Basic for Applications (VBA) code examples that demonstrate how to create and run a parameterized Microsoft Jet stored procedure.

NOTE: Native stored procedures are new in Microsoft Access 2000. However, 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 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:  In a new Microsoft Access 2000 database, create a new module. On the Tools menu, click References, and then create a reference to '''Microsoft ADO Ext. 2.5 for DDL and Security'''.  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   Run the CreateJetStoredProcedure function in the Immediate window by typing the following: CreateJetStoredProcedure   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:

<div class="references_section">