Part II: Programming Microsoft Access
494
Command objects are most valuable when working with parameterized queries. Each Command
object includes a Parameters collection containing, naturally, Parameter objects. Each param-
eter corresponds to a parameter required by the query or stored procedure referenced by the
Command’s CommandText property.
Very often the CommandText property is set to a SQL statement that includes parameters:
SELECT * FROM tblCustomers
WHERE State = ‘NY’ OR State = “NJ”
You’ll see many examples of using the ADO Command object to populate recordsets and perform
actions on data throughout this book.
The ADO Recordset object
The ADO Recordset is a very versatile object. Most often, it’s populated by executing a
Command, or directly through its Open method. OpenADORecordset1 illustrates how easily the
Recordset object opens an Access table (Open_ADO_Recordset1 is included in basADO_
Recordsets in the Chapter13.accdb example database):
Public Sub Open_ADO_Recordset1()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open “SELECT * FROM tblCustomers”, _
CurrentProject.Connection
Debug.Print rs.GetString
rs.Close
Set rs = Nothing
End Sub
In this example, the recordset is populated by opening the Customers table. Notice that a SQL
statement is used to select records from tblCustomers. The SQL statement could include
WHERE or ORDER BY clauses to filter and sort the data as it’s selected.
An alternate way to write this procedure is to use a separate statement for assigning the
ActiveConnection property:
Public Sub Open_ADO_Recordset2()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open “SELECT * FROM tblCustomers”
Debug.Print rs.GetString
rs.Close
Set rs = Nothing
End Sub
The Open_ADO_Recordset1 procedure is included in basADO_Recordsets in the
Chapter13.accdb example database.
