Access.2007.VBA.Bibl..

The ADO Object Model ..........................................................................................

The ADO object model is much simpler than the DAO object model; it is used to connect to a

wide variety of data sources, so it is not customized to Access data, as the DAO object model is.

However, with a few exceptions (working with form recordsets and creating tables programmati-

cally), you can manipulate Access data with ADO much the same as with DAO.

Connection ....................................................................................................

Although the ADO object model is not hierarchical (unlike the DAO object model), the

Connection object is the foundation object, because connections are the link to data in databases.

In DAO recordsets, the BOFproperty represents the beginning of the recordset

(Beginning Of File), and the EOF(End Of File) property represents the end.

The handy NoMatchproperty of DAO recordsets is missing from ADO; instead, you have to deter-

mine whether or not a search succeeded by examining where the cursor is. For example, in doing

a Find, starting from the beginning of the recordset (BOF) and moving forward, if the cursor ends

up at the end of the recordset (EOF), the search was unsuccessful. Here is some sample code to

illustrate this technique; if the search is successful, this means that the proposed new category

name has already been used, whereas if the cursor ends up at the end of the recordset (EOF), the

search was unsuccessful, and the new record can be created using the new category name in the

strSearchvariable (the code segment is part of the TestKeysetOptimisticprocedure,

which is listed later in this chapter):

rst.Find strSearch

If rst.EOF = False Then

strPrompt = Chr$(39) & strCategory _

& Chr$(39) & “ already used; “ _

& “please enter another category “ _

& “name”

strTitle = “Category used”

MsgBox prompt:=strPrompt, _

Buttons:=vbExclamation + vbOKOnly, _

Title:=strTitle

GoTo CategoryName

The Tables collection and form recordsets are not supported in ADO, so you will need to continue to

use DAO (either the old DAO 3.6 or the new Access 2007 DAO object model) to work with them.

If you don’t close and set to Nothing DAO database or recordset objects, it is extremely

unlikely that you will have any problems; however, if you leave ADO connections and

recordsets open, the next time you run the code, you may get this error message (with your logon

name and computer name), and you will have to close down and reopen the database to get the code

working again: “The database has been placed in a state by user ‘Admin’ on machine

‘DELL_DIMEN_8300’ that prevents it from being opened or locked.” The sample ADO procedures have

code to close any open connection or recordset in their error handlers.

TIPTIP

NOTENOTE

Part II Writing VBA Code to Exchange Data between Office Components