Access™ 2007 VBA Programmer's Reference

6.3. 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. (You can find out more about SharePoint in Chapter 17.)

6.3.1. 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, as shown in Figure 6-1.

Figure ...