MS-Access / Getting Started

New Features in DAO

As mentioned earlier, Microsoft has introduced several new features in DAO for Access 2007. These features are multi-value lookup fields, attachment fields, append-only memo fields, and database encryption. Each of these features is only available in the ACCDB file format so as not to break backward compatibility with the MDB file format.

All of these features, with the exception of database encryption, have been available on Windows SharePoint Services, and were added to Access for feature parity with that platform.

Multi-Value Lookup Fields

When you create a lookup field in Access 2007, you can optionally choose to allow that field to store multiple values. For example, say you have a table of students, and you want to track the classes that the students take. Traditionally, you accomplish this by using three tables: one for Students, one for Classes, and a table in between these two called a junction table. Multi-value lookup fields, also known as complex fields, can also be used to store the classes for a particular student as a single field in the Students table. A multi-value lookup field can store many related records in a single field value. You can think of them as an embedded or nested recordset in a field for a particular record. In fact, that's exactly how you work with multi-value lookup fields in DAO. Access displays them using a list of values.

You might look at that list and think to yourself, "Isn't that denormalized?" Well, not to worry - the values for multiple-value fields are stored behind the scenes in related tables that are not available for viewing. Access does all of the work to maintain these relationships and lets you, as the developer, focus on data manipulation in a natural manner - by using DAO.

Multi-value lookup fields can be useful for simple one-to-many relationships, but they have one major limitation. The nested recordset for a multi-value lookup field can only contain one column. This is true whether the lookup field is created using the Access interface or DAO. To extend the example a little, it might be nice to know the semester in which a student attended a particular class, and even the grade he received for the class. That is not possible using a multi-value lookup field because those fields store only one field per record.

Attachment Fields

Access has had a means for storing files in the database for some time with the OLE Object data type. However, there are a few problems with this type. The first is that Access stores a wrapper around the data, which can often result in database bloat. This is even true for linked OLE Objects. Once the data was stored in the database, it wasn't easy to retrieve outside of Access. Frequently, a form was required to display data in the field, and using DAO against OLE Object fields was not easy.

Microsoft has solved this problem by adding a new data type called Attachment. Attachment fields are a special type of multi-valued field that allow for both multiple fields and multiple values in the nested recordset. The file itself is compressed by Access and stored in the database. As such, Access can store an attachment field without the additional space required by an OLE Object. No more database bloat! A new Attachment control is also available for working with the data inside of Access.

Append Only Fields

Have you ever wanted to track the history for a particular field in a table? As requirements for maintaining data over time become more and more common, scenarios such as this may become more important. Access now enables you to store a history of the data in a memo field, using a new property called Append Only. When this property is set, Access automatically stores the previous version of text in the field as part of the data along with a timestamp. As you might imagine, this data is also stored using a multi-valued field.

Database Encryption

In previous versions of Access, you could assign a password to your database to require users to enter a password when opening a database, but that did not encrypt the data in the file. Database encryption in Access 2007 now uses Windows encryption technologies to encrypt a database when you assign a database password. This feature also replaces the encoding feature that was available in Jet.

Each of these features is covered in more detail later in the tutorial.

[Previous] [Contents] [Next]

In this tutorial:

  1. Using DAO to Access Data
  2. Data Access Objects
  3. New Features in DAO
  4. Referring to DAO Objects
  5. The DBEngine Object
  6. Using Transactions
  7. The Errors Collection
  8. The Databases Collection
  9. The CurrentDb() Function
  10. Opening an External Database
  11. Closing and Destroying Database Object References
  12. DAO Property Types
  13. Setting and Retrieving Built-In Object Properties
  14. Setting and Retrieving SummaryInfo Properties
  15. Creating Schema Objects with DAO
  16. Creating Indexes
  17. Creating Relations
  18. Creating Multi-Value Lookup Fields
  19. Database Encryption with DAO
  20. Setting Encryption Options
  21. Managing Access (JET) Security with DAO
  22. Creating Security Objects
  23. Creating and Deleting Groups
  24. Managing Passwords
  25. Data Access with DAO
  26. Modifying a QueryDef
  27. Filtering and Ordering Recordsets
  28. Navigating Recordsets
  29. BOF, EOF
  30. Navigating Recordsets with Multi-Value Lookup Fields
  31. Bookmarks and Recordset Clones
  32. Finding Records
  33. Working with Recordsets
  34. Using Arrays with Recordsets
  35. Working with Attachment Fields
  36. Append Only Fields