Provided by Allen Browne. Updated December 2008.
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.)
|Home||Index of tips||Top|