Chapter 13: Accessing Data with VBA Code
511
In this example, the recordset properties are set as part of the Open statement. Either syntax is cor-
rect; it’s completely the choice of the developer. Also, because we are directly accessing the table,
there is no way to specify an ORDER BY for the data. The data is likely to be returned in an unpre-
dictable order.
Here is another example extracting a single record, based on a CustomerID:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Source = _
“SELECT * FROM tblCustomers WHERE CustomerID = 17”
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.OpenNotice that, in Figure 13.15 rather than specifying a table, the Source property is a SQL SELECT
statement. The SQL statement used to extract records returns a single record, based on the
CustomerID. In this case, because the LockType property is set to adLockOptimistic, the
data in the record can be changed by the user.
Both CursorType and LockType are optional. If you don’t specify a CursorType or
LockType, ADO creates the recordset as an adOpenForwardOnly/adLockReadOnly type
recordset by default. This type of recordset is not updatable. If you need to make changes to the
data in the recordset, you need an understanding of the various CursorType and LockType
combinations and how they affect the capabilities of a recordset.
When you use ActiveX Data objects, you interact with data almost entirely through Recordset
objects. Recordsets are composed of rows containing fields, just like database tables. Once a
recordset has been opened, you can begin working with the values in its rows and fields.
You’ve seen recordsets many times in this book. The records returned by a query are delivered as a
recordset. Actually, when you open an Access table, Access arranges the table’s records as a record-
set, and presents it in Datasheet view. You never really “see” an Access table — you see only a rep-
resentation of the table’s data as a recordset displayed in Datasheet view.
When you open an updatable recordset — by using the adOpenDynamic or adOpenKeySet
cursor type, and specifying the adLockOptimistic lock type — the recordset opens in edit
mode.
One major difference between a table open in Datasheet view and an ADO recordset is that a
recordset provides no visual representation of the data it contains. A datasheet provides you with
rows and columns of data, and even includes column headings so you know the names of the
fields in the underlying table.
An ADO (or DAO, for that matter) recordset exists only in memory. There is no easy way to visual-
ize the data in a recordset. As a developer you must always be aware of the field names, row count,
and other data attributes that are important to your application.