IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, firstname.lastname@example.org. 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|
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...