10 key enhancements in Access 2007

This information is also available as a PDF download.

The Microsoft Access development team added more features into Access 2007 than probably any previous version. You'll definitely want to see what all the buzz is about around this latest incarnation of the popular desktop database application. Truth be told, I found it hard to pick out just 10 new features to highlight for this article.

#1: A redesigned user interface offers new tools

Virtually every aspect of the user interface in Access 2007 has been changed. If you've worked with previous versions of Access, you'll be relearning how to work within Access. This latest version includes a new Ribbon, Office menu, Quick Access toolbar, and Navigation Pane (Figure A). The latter is the replacement for the Database Window from previous versions. You need to plan for an initial decrease in your productivity as you familiarize yourself with all these new UI elements.

Figure AAccess 2007 interface

Access 2007 interface

#2: Layout view makes it easier to design your forms and reports

Access 2007 includes a new Layout view to help you design your forms and reports (Figure B). With this view, you can easily customize your forms and reports while viewing live data in the controls. You can also quickly move, resize, and align your form and report controls without having to switch to Design view. You can save valuable development time using the new Layout view to help position controls.

Figure B

#3: Report view lets you filter your reports

Access 2007 includes a new view for reports called Report view. You get a static snapshot of your data with the traditional Print Preview, but with the new Report view, you can dynamically filter the data and drill down to print only the information you need. Just right-click inside a control and select various filtering options from a contextual shortcut menu (Figure C).

Figure C

#4: Date Picker simplifies entering dates

For a long time, developers have been asking for an improved method of inputting dates into Access. This latest version includes a built-in Date Picker control you can use for data types designated as Date/Time. Access displays a small button next to these fields in datasheets and forms. When you click this button, Access displays a small pop-up calendar control, making it very easy to select a date (Figure D). On forms, you can choose a property setting (Show Date Picker) to not display this Date Picker.

Figure D

#5: Enhanced Quick Create commands help you build tables, forms, and reports

Access 2007 includes enhanced Quick Create commands that help you build new forms and reports with the click of a button. These enhanced commands create new data entry forms and reports complete with controls and graphics. The commands will help you get a jump-start on adding new objects to your database. Look for these commands on the Create tab of the Ribbon (Figure E).

Figure E

#6: Alternating row color makes your data stand out

In previous versions of Access, you had to write code to alternate the background color of records on forms and reports. Access 2007 offers a built-in feature for this, which can even be used for datasheets. The new alternating row color effect, also referred to as the "greenbar" effect, makes it easier to read your data when you have a lot of repeating rows positioned close together. You can find the Alternating Row Color button in the Design group on the Ribbon (Figure F).

Figure F

#7: New Attachment data type reduces database bloat

A common question seen in Access newsgroups and support forums is how to add attachments to records without seeing significant database bloat. Access 2007 introduces a new data type called Attachment that allows you save one or more attachments (pictures, spreadsheets, documents, and so on) with a record (Figure G). With the added benefit of better compression in this version, you can easily add pictures to your records where appropriate. The Access development team added this new data type to align better with Microsoft Windows SharePoint Services.

Figure G

#8: Multi-value field lookups ease the pain of creating many-to-many relationships

Lookup fields display one value in a field but store a different value. Access 2007 adds onto the existing concept of lookup data types by allowing you to create multi-value field lookups to handle complex data. With multi-value field lookups, you can store multiple values in a single lookup field. For example, in a field of Trained Positions, you could store all the trained job code positions for each employee. Whenever you use a multi-value field lookup, Access 2007 displays a special control that looks like a hybrid combo box-list box control (Figure H). When you click the down arrow on the control, you can individually select each of the related records. Hidden from the user interface, Access 2007 creates the appropriate many-to-many relationships, including the third junction table, to handle the complex relationship.

Figure H

#9: New feature lets you collect and import data via e-mail

An exciting new feature in Access 2007 is the ability to collect data through e-mail and import it into your database. You can use forms created with HTML or rich forms created with Microsoft Office InfoPath 2007. If you use HTML, your recipients need only have an e-mail client program that accepts HTML. If you use the InfoPath forms, you and your recipients must all be using Microsoft Office Outlook 2007 and InfoPath 2007. Why spend extra time updating membership records when your members can update their own records?! A wizard walks you through the steps necessary to create your data collection forms (Figure I).

Figure I

# 10: Finally, rich text is built into Access

For all previous versions of Access, developers had to rely on third-party controls to have rich text capabilities — but no longer! Access 2007 includes the ability to have rich text in text and memo fields (Figure J). This formatting is stored as HTML. Go ahead and add in some fancy colors, bold face, italics, underlines, bullets, and more.

Figure J

Jeff Conrad was awarded Microsoft's Most Valuable Professional award from 2005 to 2007 for his continual involvement with the online Access community. He is co-author of Microsoft Office Access 2007 Inside Out and is a contributing author of Microsoft Office 2007 Inside Out. Because Jeff knows Access "Inside Out," he recently joined Microsoft as a Software Design Engineer in Test working with Access and Excel technologies. To learn more, visit his Web site at

Additional Office 2007 "10 things" resources

Editor's Picks