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.
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.
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.
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields