Access.2007.VBA.Bibl..

Create a connection to an external database.

strDBName = “D:\Documents\Northwind.mdb”

Set cnn = New ADODB.Connection

Set rst = New ADODB.Recordset

strQuery = “qryCategorySalesFor1997”

Need to specify the Jet 4.0 provider for connecting to Access databases.

With cnn

.Provider = “Microsoft.Jet.OLEDB.4.0”

.Open strDBName

strConnectString = .ConnectionString

End With

Open a recordset based on a saved query.

rst.Open Source:=strQuery, _

ActiveConnection:=cnn, _

CursorType:=adOpenStatic, _

LockType:=adLockReadOnly

Once the recordset has been created, you can work with it much like a DAO recordset, though

there are some differences — see the sections on ADO recordset cursor and lock types for details on

the differences.

For further information on converting DAO code to ADO code see Alyssa Henry’s arti-

cle “Porting DAO Code to ADO with the Microsoft Jet Provider,” which is available

online in the MSDN Library by searching its title or at http://msdn.microsoft.com/library/

default.asp?url=/library/en-us/dndao/html/daotoado.asp.

Summary ..........................................................................................................................

The DAO object model was developed to work with Access data, and (despite rumors of its death,

which have been heard for many versions now, and heavy promotion of the alternative ADO object

model) DAO is still the best object model for working with data in Access tables. In Access 2007

instead of removing the DAO object model, Microsoft wisely chose to trim it of some rarely used

components and rename it the Microsoft Office 2007 Access database engine object model. My rec-

ommendation is to use DAO for all tasks involving Access data.

When you need to work with data in other types of databases or data sources, however, ADO is the

object model you need to use (no choice there — DAO only works with Access data). ADO can be

used to work with Access data as well, though it has some limitations compared to DAO, so in

some cases you may want (or need) to use ADO to work with Access data; I have provided infor-

mation on converting DAO code to ADO for these situations.

TIPTIP

Working with Access Data 5