The DAO Object Model

        8 votes: *****     14,76 views      No comments
by Allen Browne, 20 April 2005    (for Access v2+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne, 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.

                   |                          |           |
               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

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)

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:

Data Access Objects (DAO) diagram

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.)

Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'The DAO Object Model'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).