Chapter 13: Accessing Data with VBA Code
485
The fields specified in the ORDER BY clause do not have to be the same fields specified in the
SELECT clause. You can sort by any of the fields in the tables specified in the FROM clause. In fact,
the fields in the ORDER BY clause don’t have to be included in the query’s results. Uncheck the
Show checkbox in the QBE grid to exclude the field from the query’s results.
Working with Data
The first thing to note when discussing data access objects is that the DAO and ADO object models
are separate from the Access object model. DAO and ADO represent the objects managed and
“owned” by the Access database engines (ACE or Jet), which are software components installed
along with Office. In the past, Excel (with the MSQuery add-on) and Visual Basic (the stand-alone
application development product) could directly use the Jet database engine or access it through
open database connectivity (ODBC) or Microsoft Query.
Using Access VBA enables you to manipulate your database objects behind the scenes, giving you a
great amount of flexibility within your applications. Access provides two different object models
for working with data: ADO and DAO.
ADO (ActiveX Data Objects) is the newer of the two syntaxes. It’s based on Microsoft’s ActiveX
technology, which provides the basis for independent objects that perform complex tasks without
input from their hosts. When applied to ADO, the ActiveX objects are able to perform a wide vari-
ety of data access tasks without hampering Access in any way. Because ADO objects are quite pow-
erful, the ADO object model (meaning, the ADO object hierarchy) is fairly sparse. Only a few
objects are needed to perform virtually all data access tasks in Access applications.
The older data access object model supported by Access is DAO (Data Access Objects). Unlike
ADO, DAO objects are simple and direct, and require a bit more VBA code to establish and main-
tain. DAO is widely used and was the only data access methodology in Access for many years.
The distinction between Access and DAO is important because Access’s user interface tends to blur
the line between objects belonging to Access and those belonging to the database engine. There are
some features available in code that you may think are data access objects but are really features of
Access, and vice versa. In code, you’ll have to develop with this distinction in mind. For example,
ADO and DAO objects have many built-in properties and methods; other properties are added by
Access.
In any case, working with ADO and DAO in VBA procedures provides you with much greater flex-
ibility than dealing strictly with forms and reports bound to queries and tables. As you’ll see in the
rest of this chapter, relatively few lines of ADO or DAO code perform complex operations on data,
such as updating or deleting existing records, or adding new records to tables. Using VBA code
means that an application can respond to current conditions on a form, such as missing or incor-
rect values. It’s quite easy to perform ad hoc queries against data that would otherwise require
complex queries with many parameters.