HandsOn Executing a Select Query with an ADO Catalog Object

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

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

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

Sub Execute_SelectQuery2() Dim cat As ADOX.Catalog Dim cmd As ADODB.Command Dim rst As ADODB.Recordset Dim strPath As String strPath = CurrentProject.Path & "\Northwind.mdb"

Creating and Manipulating Databases with ADO

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

Set cmd = New ADODB.Command

Set cmd = cat.Views("Products by Category").Command Set rst = New ADODB.Recordset rst.Open cmd, , adOpenStatic, adLockReadOnly, adCmdTable Debug.Print rst.GetString

MsgBox "The query returned " & rst.RecordCount & vbCr & _

" records to the Immediate window." rst.Close Set rst = Nothing Set cmd = Nothing Set cat = Nothing

End Sub

Notice that the following line of code is used to indicate the name of the query to be executed:

Set cmd = cat.Views("Products by Category").Command

This statement sets the cmd object variable to the desired query stored in the Views collection of the ADOX Catalog object.

Next, the Open method of the Recordset object is used to open the recordset based on the specified query:

rst.Open cmd, , adOpenStatic, adLockReadOnly, adCmdTable

Notice that several optional arguments of the Open method are used to specify the data source: cmd, ActiveConnection (a comma appears in this spot because the existing connection is being used), CursorType (adOpenStatic), LockType (adLockReadOnly), and Options (adCmdTable). Refer to Chapter 13 for information about using these ADO constants.

Next, the procedure dumps the contents of the records into the Immediate window (just as the procedure in Hands-On 15-2 did) by using the recordset's GetString method. The MsgBox function contains a string that includes the information about the number of records retrieved. The RecordCount property of the Recordset object is used to get the record count. To get the correct record count you must set the CursorType argument of the recordset's Open method to adOpenStatic. If you set this argument to adOpenDynamic or adOpenForwardOnly, the RecordCount property will return -1. To learn more about these constants, refer to the sections on working with Recordset objects in ADO in Chapter 13.

Part II

0 0

Post a comment