Microsoft Office Tutorials and References
In Depth Information
Chapter 5: Using SQL and Recordsets
Chapter 5: Using SQL and
Recordsets
In This Chapter
Creating recordsets
Using SQL to create recordsets
Running action queries from VBA code
Working with data in tables and queries through Visual Basic for
Applications (VBA) is — in a word — weird. You don’t exactly work
with a table or query directly in VBA. Instead, you work with a recordset. As
the name implies, a recordset is a set of records. A recordset can be all the
records in a given table, all the records in the results of a query involving two
or more tables, or a subset of particular records from any table or query. In
other words, a recordset can contain any records from any tables you want.
Recordsets and Object Models
Because Access offers two object models for the purpose of working with
recordsets, you may find recordsets to be confusing. One object model is
DAO (Data Access Objects); the other is ADO (ActiveX Data Objects). The DAO
model is the older of the two. DAO works only with Access tables. ADO, the
newer of the two, works either with Access tables or external data sources,
such as Oracle and Microsoft SQL Server.
At first glance, you may think, “Well, I’ll never use external data sources, so I’ll
stick with the DAO object model.” Picking an object model isn’t that simple,
though. Microsoft currently favors the newer ADO model, meaning that ADO
will continue to grow and get better while DAO remains in maintenance mode,
which generally spells doom for a technology. If a technology is in maintenance
mode today, that fact pretty much guarantees that it won’t exist in the not-too-
distant future.
Given the bias of Microsoft, we stick with ADO in this topic. To ensure that
the stuff that we do in this chapter works on your computer, make sure the
ADO object model is loaded in your copy of Access. To do so, open Visual
Basic Editor, choose Tools References to open the References dialog box,
and select the Microsoft ActiveX Data Objects 6.1 Library option (see Figure
5-1). If you don’t see that option, you’ll have to scroll down to find it.
Search JabSto ::




Custom Search