Part II: Programming Microsoft Access
486
Entire books — big books — have been written on the topics covered in the remainder of this chap-
ter. All I can do in this chapter is provide you with some fundamental examples of using ADO and
DAO in Access applications, and, coupled with the material in the other chapters in this book, you
should be well prepared to incorporate VBA-based data management in your Access applications.Note
ADO and DAO are not equivalent in every regard. Both syntaxes enable you to add to or modify the data in
tables, build recordsets, work with data in recordsets, and populate forms with data. However, ADO has a dis-
tinct edge when it comes to working with external data sources. As you’ll soon see, ADO requires a provider
that defines the data source used by the ADO objects in an application. ADO providers are specific to the data
source, such as SQL Server or Access. The provider endows the ADO objects with special abilities (such as the
ability to test the connection to the data source), depending on the underlying data sources. DAO, on the other
hand, is a more generic data access syntax and is not specific to any one data source. ADO is the logical choice
where advanced data access tasks must be performed, while DAO is very good at routine querying, updating,
and other data tasks.
The following sections describe each of these objects and explain how each object adds to the ADO
data access capabilities.Although Access is not strictly object oriented, it is most certainly object based. The remainder of
chapter describes the object models you use in VBA code to perform data-management tasks in your
Access applications. An object model is simply the arrangement of the objects that perform the
data-management tasks. A sound understanding of the ADO and DAO object models is an essential
requirement when using VBA code to manage Access data.Many of the objects described in this chapter contain a collection of zero or more objects. A collection
is a container holding all the members of a certain type of object. (A collection is, itself, an object.)A collection is like a stack of baseball cards. Each card in the stack is different from all the other
cards, but all baseball cards have certain characteristics (like size, the statistics printed on the back,
and so on) in common. In Access, a recordset object (either ADO or DAO) contains a collection of
field objects. Every recordset object shares certain characteristics with all other recordset objects,
and every field object is similar to all other fields in certain ways.The name of a collection is almost always the plural of the object type within the collection.
Therefore, a Fields collection contains a number of different Field objects.Note
It’s important to know when a term applies to an object of the same name or is just the name of a general cate-
gory of database items. In this book, a capitalized word like Field refers to a Field object, in contrast to field
(lowercase), which is a generic reference to any field in any table. Similarly, Fields means a Fields collection,
while fields refers to a number of different fields.
Each ADO or DAO object comes with a collection of properties and methods. Each property or
method provides you with a way to define the object, or represents an action you use to direct the
object to perform its job.