HandsOn Executing a Parameter Query

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

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

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

Sub Execute_ParamQuery() Dim cat As ADOX.Catalog Dim cmd As ADODB.Command Dim rst As ADODB.Recordset Dim strPath As String strPath = CurrentProject.Path & "\Northwind.mdb" Set cat = New ADOX.Catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPath

Set cmd = New ADODB.Command

Set cmd = cat.Procedures("Invoices Filter").Command

' specify a parameter value cmd.Parameters("Forms!Orders!OrderID") = 10258

' use the Execute method of the Command object to open the recordset Set rst = cmd.Execute

' return product names to the Immediate window Do Until rst.EOF

Debug.Print rst(20).Name & ": " & rst(20) rst.MoveNext Loop rst.Close Set rst = Nothing Set cmd = Nothing Set cat = Nothing End Sub

The Execute_ParamQuery procedure shown above establishes the connection to the Northwind database. Next, the name of the query is supplied in the following statement:

Set cmd = cat.Procedures("Invoices Filter").Command

Creating and Manipulating Databases with ADO

Because this is a parameter query, the parameter value is specified by using the Parameters collection of the Command object, like this:

cmd.Parameters("Forms!Orders!OrderTD") = 10258

^^ Note: Instead of specifying the parameter values before the recordset is open, you can use the Parameters argument of the recordset's Open method to pass the parameter value, as shown below:

Set rst = cmd.Execute(Parameters:=10258)

Then, the Recordset object is opened by using the Execute method of a Command object:

Set rst = cmd.Execute

Finally, the procedure loops through the recordset to retrieve the contents of each record's 20th field (ProductName) and print them to the Immediate window. After running this procedure, the following lines are returned to the Immediate window:

ProductName: Chang

ProductName: Chef Anton's Gumbo Mix

ProductName: Mascarpone Fabioli

SQL pass-through queries are SQL statements that are sent directly to the database server for processing. To create a pass-through query manually, you need to activate the Query Design screen and choose Query | SQL Specific | Pass-Through. This will bring up a window where you can type a query statement like the one shown in Figure 15-4. The SQL statement must be in the format understood by the external data source from which you are retrieving data. Pass-through queries can also be used in lieu of Action queries when you need to bulk append, update, or delete data in remote databases.

0 0

Post a comment