HandsOn Creating a Parameter Query

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Create_ParameterQuery procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Create_ParameterQuery() Dim cat As ADOX.Catalog Dim cmd As ADODB.Command Dim strPath As String Dim strSQL As String Dim strQryName As String

On Error GoTo ErrorHandler strPath = CurrentProject.Path & "\Northwind.mdb"

strSQL = "Parameters [Type Country Name] Text;" & _ "SELECT Customers.* FROM Customers WHERE " _ & "Customers.Country=[Type Country Name];"

strQryName = "Customers by Country"

Set cat = New ADOX.Catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPath

Set cmd = New ADODB.Command cmd.CommandText = strSQL

Creating and Running Queries with ADO | 275 Creating and Manipulating Databases with ADO

cat.Procedures.Append strQryName, cmd Set cmd = Nothing Set cat = Nothing

MsgBox "The procedure completed successfully.", _

vbInformation, "Create Parameter Query" Exit Sub

ErrorHandler:

If InStr(Err.Description, "already exists") Then cat.Procedures.Delete strQryName Resume Else

MsgBox Err.Number & ": " & Err.Description End If End Sub

0 0

Post a comment