HandsOn Executing a Select Query

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

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

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

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

Set cmd = New ADODB.Command With cmd

.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & strPath .CommandText = "[Products by Category]" .CommandType = adCmdTable End With

Set rst = New ADODB.Recordset Set rst = cmd.Execute

Debug.Print rst.GetString rst.Close

Set rst = Nothing

Set cmd = Nothing

MsgBox "View results in the Immediate window." End Sub

Part II

In the Execute_Select Query procedure above, the connection to the database is opened by setting the ActiveConnection property of the Command object.

Next, the Command object's CommandText property specifies the name of the query you want to run. Notice that you need to place square brackets around the query's name because it contains spaces.

The query type is determined by setting the CommandType property of the Command object. Use the adCmdTable or adCmdStoredProc constants if the query string in the CommandText property is a query name.

Finally, the Execute method of the Command object executes the query. Notice that the resulting recordset is passed to the Recordset object variable so that you can access the records retrieved by the query. Instead of looping through the records to read the returned records, the procedure uses the Recordset object's GetString method to print all the recordset rows to the Immediate window. The GetString method returns the recordset as a string (for more information refer to Chapter 13).

Figure 15-1: This is a sample result of records that were generated by executing a Select query (see Hands-On 15-2).

The next example procedure (Hands-On 15-3) demonstrates another method of running a row-returning query with ADO. Notice that in addition to the ADO Command and Recordset objects, this procedure uses the ADOX Catalog object. The connection to the database is established by setting the ActiveConnection of the Catalog object, and not the Command object, as was the case in the preceding example (Hands-On 15-2).

0 0

Post a comment