Microsoft Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com. Updated December 2008.

DAO Object Model

DAO (Data Access Objects) is the object model that lets you get at the heart of Access programmatically. If you have problems working with these objects in Access 2000 or 2003, see Solving problems with References.

Learning how to refer to these objects opens up a simple, consistent way of finding the structure and current state of just about anything in your database. The enclosed diagram is so useful that you may Access programmers using it for wallpaper. There's a graphical version (5K) at the end of this article.

                          dbEngine
                             |
                         Workspaces
                    _________|____________________________
                   |                          |           |
               Databases                    Users       Groups
                   |                          |           |
                   |                        Groups      Users
       ____________|__________________________
      |                      |                |
  TableDefs              QueryDefs            |
   ___|____             _____|_____           |
  |        |           |           |          |
Fields  Indexes      Fields   Parameters      |
           |                                  |
         Fields                               |
                                  ____________|___________
                                 |            |           |
                             Recordsets   Relations   Containers
                                 |            |           |
                               Fields       Fields    Documents

At the top of the diagram, the dbEngine object contains Workspaces, and workspaces contain Databases. Within Access, only one workspace can be open, so the current database is always Workspaces(0).Databases(0). Before you can refer to other objects, you need a database type variable, so DAO references usually begin with:

Dim db as database
Set db = dbEngine(0)(0)  'The (0)(0) is short for Workspaces(0).Databases(0)

Next the diagram tells us that databases contain, amongst other things, TableDefs. Each object has properties such as Name or Count, so we can learn the number of tables (including system tables) from db.TableDefs.Count. The tables can be referred to by name, e.g. db.TableDefs![tblClients], or by number e.g. db.TableDefs(0).

Since the diagram shows that TableDefs have Fields, we could learn the number of fields in the first table from db.TableDefs(0).Fields.Count. To list the fieldnames, try

For i = 0 to db.TableDefs(0).Fields.Count - 1
   Debug.print db.TableDefs(0).Fields(i).Name
Next

To learn what properties any object has, loop through its "properties" property, e.g.

For i = 0 to db.TableDefs(0).Properties.Count - 1
   Debug.print db.TableDefs(0).Properties(i).Name & " = ";
   Debug.print db.Tabledefs(0).Properties(i)
Next

Have we whet your appetite yet? Many properties can be altered from code, and you can append or delete objects such as Fields, Indexes, QueryDefs, and even custom properties. Have fun exploring!

Grab this image if it's useful to you:

For a couple of dozen more examples of how to use DAO, see DAO Programming Code Examples.

For more details, see Microsoft's DAO reference (MSDN.)