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