| Microsoft Office 2000/Visual Basic Programmer's Guide | |
Saved queries that return records in an Access database may have parameters (a parameter query) or not have parameters (an Access select query). The following sections describe how to use an ADO Recordset object to work with each kind of saved query.
To open a Recordset object by using a saved query that returns records and has no parameters, pass the query's name as the source argument to the Open method of the Recordset object, as shown in the following example.
Sub RunSavedQuery(strDBPath As String _
strQryName As String)
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection.
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath
End With
Set rst = New ADODB.Recordset
With rst
' Open the query by using a forward-only, read-only Recordset object.
.Open Source:= strQryName, _
ActiveConnection:= cnn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly
' Display the records in the Debug window.
Do While Not .EOF
For Each fld In .Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
.MoveNext
Loop
'Close the Recordset object.
.Close
End With
Close connection and destroy object variables.
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
For example, to use a Recordset object to open the Products Above Average Price query in the Northwind database, you can use a line of code like this:
RunSavedQuery _
"c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
"[Products Above Average Price]"
Note that with ADO, if the query name contains spaces, you must use either square brackets ([ ]) or the accent grave character (`) around the name.
The RunSavedQuery procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.
One way of running a query that has parameters (or a "parameter query") is to use ADOX code to retrieve the query as a Command object from the database's Procedures collection, and then specify the parameters by using the Command object's Parameters collection. The following procedure shows how to do this.
Sub RunParamQuery(strDBPath As String, _
strQryName As String, _
strParamName1 As String, _
varParamValue1 As Variant, _
strParamName2 As String, _
varParamValue2 As Variant)
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection.
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath
End With
' Open the catalog.
Set cat = New ADOX.Catalog
cat.ActiveConnection = cnn
' Get the Command object from the Procedures collection.
Set cmd = New ADODB.Command
Set cmd = cat.Procedures(strQryName).Command
' Specify the parameter values.
With cmd
.Parameters(strParamName1) = varParamValue1
.Parameters(strParamName2) = varParamValue2
End With
Set rst = New ADODB.Recordset
With rst
' Open the Command by using a forward-only, read-only Recordset object.
.Open Source:= cmd, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly
' Display the records in the Debug window.
Do While Not .EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
.MoveNext
Loop
' Close the Recordset object.
.Close
End With
' Close connection and destroy object variables.
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
For example, to use a Recordset object to open the Employee Sales by Country query in the Northwind database and pass in parameter values, you can use a line of code like this:
RunParamQuery "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
"Employee Sales by Country","[Beginning Date]",#8/1/96#,"[Ending Date]",#8/31/96#
The RunParamQuery procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.
You can use fewer lines of code by specifying the parameter values with the Parameters argument of the Command object's Execute method. The following lines of code
' Specify the parameter values.
With cmd
.Parameters(strParamName1) = varParamValue1
.Parameters(strParamName2) = varParamValue2
End With
Set rst = New ADODB.Recordset
With rst
' Open the Command by using a forward-only, read-only Recordset object.
.Open Source:= cmd, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly
can be replaced by these lines:
Set rst = New ADODB.Recordset
' Execute the Command, passing in the values for the parameters.
Set rst = cmd.Execute Parameters:=Array(varParamValue1, varParamValue2)
You can also run a parameter query without using any ADOX code by connecting a Command object to the database, and then setting its CommandText property to the query's name. In this case, you'll also need to set the provider-specific Jet OLEDB:Stored Query property of the Microsoft Jet 4.0 OLE DB Provider to True to indicate that the command text is the name of a stored query. By default, the Microsoft Jet 4.0 OLE DB Provider evaluates the CommandText as an SQL string. In the previous examples that use ADOX code, this was not necessary because a query is automatically recognized as a stored query when it is retrieved from the Procedures or Views collection. The following procedure shows a complete example of how to run a parameter query without using ADOX code.
Sub RunParamQueryNoADOX(strDBPath As String, _
strQryName As String, _
varParamValue1 As Variant, _
varParamValue2 As Variant)
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath
End With
' Create the command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
With cmd
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = strQryName
End With
' Execute the command and pass in the values for the parameters.
Set rst = New ADODB.Recordset
Set rst = cmd.Execute(Parameters:=Array(varParamValue1, varParamValue2))
With rst
' Display the records in the Immediate pane.
Do While Not rst.EOF
For Each fld In .Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
.MoveNext
Loop
' Close the Recordset object.
.Close
End With
' Close connection and destroy object variables.
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
To use this procedure to open the Employee Sales by Country query in the Northwind database, you don't need to pass in the parameter names, just the parameter values, like this:
RunParamQueryNoADOX _
"c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
"Employee Sales by Country",#8/1/96#,#8/31/96#
The RunParamQueryNoADOX procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.