Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

510

As a data access interface, ADO allows you to write programs to manipulate data in local or remote

databases. Using ADO, you can perform database functions including querying, updating, data-

type conversion, indexing, locking, validation, and transaction management.

Here is a fragment of a procedure showing how to use the ADO Recordset object to open a

table:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.ActiveConnection = CurrentProject.Connection

rs.Source = “tblCustomers”

rs.CursorType = adOpenDynamic

rs.LockType = adLockOptimistic

rs.Open

The ADO Recordset object provides the Open method to retrieve data from a table or query. A

recordset is simply a set of records from a database table or the set of records returned by a query.

The Open method has four parameters:

l Source: The data source to open. Source can be the name of a table (as in this exam-

ple), the name of a query, or a SQL statement that retrieves records. When referencing a

table, the table can be a local or linked table.

l (^) ActiveConnection: Refers to a connection to a database. A connection is a communi-
cation line into the database. CurrentProject.Connection refers to the current
Access database.
l CursorType: ADO supports a number of different cursor types. A cursor is a pointer, or
set of pointers, to records. Think of a cursor the way ADO keeps track of records.
Depending on the property settings used to retrieve data, ADO cursors can move only for-
ward through records (adOpenForwardOnly), or permit forward and backward move-
ment (adOpenDynamic). A dynamic cursor (adOpenDynamic) allows movement in
both directions, while adOpenForwardOnly permits only forward movement. (The
CursorType property is explained in detail in the “Understanding CursorType” sec-
tion, earlier in this chapter.)
l LockType: Determines how ADO locks records when updating. adLockOptimistic
allows other users to work with a record that is locked by the ADO code, while adLock-
Pessimistic completely locks other users out of the record while changes are made to
the record’s data.
This same ADO statement can be rewritten in a somewhat more condensed fashion:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open “tblCustomers”, CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic