Chapter 6. Using DAO to Access Data

As you've seen from previous chapters, VBA is the programming language you use to programmatically interact with the Access object model. You use VBA to manipulate Access-specific objects, such as forms, reports, and so on. But since Access is a Relational Database Management System, you will undoubtedly find yourself also needing to programmatically interact with the data it contains, and indeed with the database design, or schema. Microsoft Access employs two data access object models: Data Access Objects (DAO) and ActiveX Data Objects (ADO). While Chapter 7 describes ADO, this chapter is solely concerned with examining the DAO model.

We'll begin this chapter with a brief history of DAO and an indication of when it might be most appropriate to use DAO in preference to ADO. We'll introduce the three most important objects in the DAO object hierarchy: the DBEngine, Workspace, and Database objects. We'll then talk about database properties and how to use them.

Before we start working with DAO objects to access our data, we'll take an in-depth look at how to use DAO to create and modify our database structure, including tables, fields, indexes, and relations. We'll then spend some time discussing the Jet (Joint Engine Technology) security model, and how you can create and manipulate security objects, such as users, groups, and of course, how to read and assign object permissions. Finally, we'll examine data access in detail using QueryDefs and Recordsets. This will include many examples of how to create data access objects, and how to find and manipulate their data.