Software

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 http://www.AccessJunkie.com.

Additional Office 2007 "10 things" resources

8 comments
contactttc
contactttc

Awesome thumbnail view of AC 2007. I already have a use for about 4 of the 10 items listed here. Thanks

tariq_jamil
tariq_jamil

Still no support to print memo fields with 'text justify'.

citatech
citatech

I recently created a simple database that has a form showing images stored in the attachments field of my database. I have about 235 records, each containing 1 image attachment ranging in size from 100KB to 3.5MB per image. As I manually added these images as attachments, my database size increased. My images alone total 220MB, and my database with all those attached images is now 164MB. It did compress them, but not enough for my liking, so I wanted to remove all attachments to get my database down to a smaller size (like 10MB)... maybe try linking with filename/path instead, BUT even after manually removing over 70 images, saving and closing, my database did not decrease a single KB! I can't get rid of that data. I even tried removing the attachment field in design view. It removes it, but the database size is still 164MB no matter what I do. Where is all that data being stored and how can I clean it out of my database to decrease the size? The only help I can find or mention of attachments is to double click the attachment for a record, hit "Remove" and then "OK". I've done that over and over for more than 70 records and that does not decrease the db size at all! Help would be much appreciated.

jonj
jonj

Everybody is gladly regurgitating the "key enhancements" in Access 2007, and one of those is the long overdue built-in datepicker, but I see no way to make it handle the time, too; The field is named "Date/Time", after all, because each value represents an exact moment in the past, present, or future. How is this picker supposed to be of value to us, if it does not handle the full depth of the job it is intended for? Any ideas?

claire.timlin
claire.timlin

Hi, I too am having this problem and I was wondering if you managed to fix it. The "Compact and Compare" does not work for me. Does anyone know where access stores attachments? Maybe it is possible to find them and delete them (even though they have been supposedly removed already!). Thanks, Claire

Data Nut
Data Nut

Have you tried Compact & Repair yet? It may clean it up a bit for you. It's now accessed in the Office button, in the Manage category. Beware though, if you don't have the March 6 (?) hotfix applied Access will take the compressed version of your database and name it something like "Database1.mdb" and the non-compacted version of your database will remain with the original name. Hope this helps!

Data Nut
Data Nut

I'm not wild about the new ribbon, but I'm getting used to it and I'm actually starting to like it. BUT... My biggest beef is with the new Access Objects interface. Now you can only use Ctrl-F6 to toggle between them (used to be Alt-Tab) and you can't see what you're opening prior to releasing the keys like with Alt-Tab. Also, when you minimize objects in the workspace, there is no hover feature to show you the object's full name, so you have to open the object to see if it's what you want. The other major problem with the Access Objects interface is it's always on top. It's a major waste of real estate (along with the ribbon). This feature should not be always on top when expanded - it should act like other windows - click on it to bring to the foreground. I should not have to shrink and expand it to make space on the workspace so I can work with my other objects. The other issue is the contrast between text and the background in Office. If you take a screen snip and zoom in with Paint, you'll see there is no black - mostly purple and other colors thrown in to smooth their appearance on the display. I'm finding it harder to read, it seems almost blurry, and my eyes are tiring quicker. I wonder in that 3rd party Classic menu add-on does anything for these issues. Bottom line - it looks like Microsoft was much more interested in the pretty and less on the usability.

Data Nut
Data Nut

Microsoft created a Hotfix for this problem. Here's the link. http://support.microsoft.com/kb/949404 Attachments - haven't played with that feature yet. If the attachments are of any significant size though I'd be careful not to max out the db, esp. if it's difficult to remove them. 2GB isn't much space anymore!

Editor's Picks