Transitioning Your Existing Access Applications to Access 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn about some of the new features in Access 2007 and how you can take advantage of them to improve your existing Access applications. Read about some of the issues you should consider when converting your databases to the new ACCDB format. (18 printed pages)

Jan Fransen, A23 Consulting

Ken Getz, MCW Technologies

December 2006

Applies to: Microsoft Office Access 2007, Microsoft Office Access 2003, Microsoft Access 2002, Microsoft Access 2000

Contents

  • Overview of Transitioning Applications to Access 2007

  • Opening Your Access 2000, Access 2002, or Access 2003 Application in Access 2007

  • Deciding Whether to Change Database Formats

  • Visual and User Interface Changes

  • Replacing Code and Third-Party Controls

  • Data Handling Changes

  • Conclusion

  • Additional Resources

Overview of Transitioning Applications to Access 2007

By now, you have almost certainly read about, seen, or experimented with the new user interface (UI) in the 2007 Microsoft Office system. The features you hear about most often, such as the Ribbon and the Quick Access Toolbar, are intended to make Office system applications more approachable for interactive users. You might know that the Microsoft Office Access team added several features for developers. You can see examples of many of these new features in the sample databases and templates that are included with Microsoft Office Access 2007, all of which were designed for Access 2007. What you may not be sure about is how (or whether) an application you developed for an earlier version of Access will work, and what it will look like, when you first open it in Access 2007. You might also wonder whether you can use these new features without needing to completely rewrite your applications.

In this article, we answer some of the questions you might have about your existing applications, including questions about the following:

  • Features you (and your users) will notice when opening an application in Access 2007 that was written in an earlier version of Access.

  • When to convert your existing Access database to the new Access 2007 format (and when you should not).

  • How you can take advantage of new Access 2007 features to improve the visual appeal and UI of your existing application.

  • Changes you can make to eliminate unnecessary Microsoft Visual Basic for Applications (VBA) code or ActiveX controls from your application.

The focus of this article is to demonstrate how you can make the best use of some of the new features in Access 2007 without rewriting your existing application. Along the way, we also point out some features that you might consider for a new version of your application, or for your next new application.

To demonstrate how you can migrate an existing application to take advantage of new Access 2007 features, we use the familiar Northwind sample database in its Microsoft Access 2000 incarnation. This version was included with Access 2000, Microsoft Access 2002, and Microsoft Office Access 2003 (but not with Access 2007, in which it is replaced with a newer version).

Opening Your Access 2000, Access 2002, or Access 2003 Application in Access 2007

You can open applications written in an earlier version of Access in Access 2007, and the applications work just as they did in Access 2003, with a few exceptions. In addition to the obvious UI changes, such as the Navigation Pane and the Ribbon, you see different security messages than you did from Access 2003 and you see changes to how Access displays custom menus. Access 2007 also dropped support for one of the Access objects, the DataAccessPage object.

Security Changes

When you open your application in Access 2003, you might see one or more security-related dialog boxes. The number of dialog boxes and the choices they contained depended on your installation of Access and whether the database being opened was digitally signed. In Access 2007, you do not see any dialog boxes when you open a database. Instead, if you open an unsigned database, you might see a security alert such as the one shown in Figure 1. The alert is displayed under the Ribbon in an area called the Business Bar.

Figure 1. Security alert that replaces Access 2003 dialog boxes

A security alert replaces Access 2003 dialog boxes

Whether you see the security alert depends on your macro settings, as it is with the original Security Warning dialog box. In Access 2007, you can change your macro settings by clicking the new Microsoft Office Button (see Figure 2) in the upper-left corner of the screen and then clicking Access Options at the bottom of the menu. The Microsoft Office Button provides access to file-related actions and options that apply to your Access installation.

Figure 2. Microsoft Office Button

The Microsoft Office Button

In the Access Options dialog box, click the Trust Center tab, and then click Trust Center Settings. In the Trust Center dialog box, select the Macro Settings tab. You see the choices shown in Figure 3.

Figure 3. Choosing whether to run macros and whether you receive notification if macros are disabled

Choosing whether to run macros

<<<<<<< HEAD

Note

As in earlier versions of Access, the word macro in the context of Microsoft Office security refers to VBA code, not Access macros.

=======

NoteNote

As in earlier versions of Access, the word macro in the context of Microsoft Office security refers to VBA code, not Access macros.

>>>>>>> 7f3a9c6c13e671f21bf63e8e33d124610e43b846

The Disable all macros with notification option is probably the most useful choice, especially because the security alert that Access 2007 displays provides two quick and easy ways to enable code after a database is loaded. To enable code only for the current session, click Enable Content next to the security alert message. You see the Trust in Office dialog box, as shown in Figure 4. Select Enable the content, and then click OK to enable the code only for this session.

Figure 4. Trust in Office dialog box lets you temporarily enable content

Trust in Office dialog box

If you want to determine what happens when you load this database (or any database) and persist the settings, open the Trust Center dialog box by clicking Trust Center next to the security alert message. In addition to changing macro security settings, you can also use the Trust Center to add trusted publishers or trusted locations to your installation of Access.

If you are concerned about end users seeing the security alert rather than your startup form, you can take advantage of the fact that an untrusted database can run a limited set of Access macro actions. You can use a macro at startup to check the trust status and take different actions depending on that status.

Access 2007 supports the AutoExec macro just as Access always has: If you have an Access macro named AutoExec in the database, that macro runs automatically when the database is opened. Access 2007 provides a new property of the CurrentProject object, the IsTrusted property, so that you can check whether the rest of your application can load. You can then display a message (using a form that contains no code, or a message box) telling the user what to do to run the application. The macro definition could look similar to Figure 5.

Figure 5. Many macro actions can run even when a database is untrusted

Many macros can run when a database is untrusted

Custom Menu Bars

In earlier versions of Access, you can create your own custom menu bars, and you can even replace the default menu bar with a custom menu bar. In Access 2007, the behavior of your custom menu depends on the existing startup options: When you open a database that both contains a custom main menu bar and hides the built-in toolbars, Access displays your menu in place of the standard Access 2007 Ribbon. If your database's startup options do not disable the built-in toolbars, Access adds a new tab to the Ribbon named Add-Ins. Your custom menu is displayed on the Add-Ins tab, as shown in Figure 6.

Figure 6. Custom menus displayed on Add-Ins tab

The Add-Ins tab displays custom menus

You can find the custom menu bar option, and other startup options, on the Current Database tab of the Access Options dialog box.

Data Access Pages

In earlier versions of Access, data access pages provided a way for you to develop browser-based pages that displayed Access data. If your application contains data access pages, you will still see them in the Navigation Pane, but Access 2007 does not provide a Data Access Page designer. If you open a data access page from Access 2007, Access starts an instance of Internet Explorer and opens the data access page in that instance. If you want to change the design of your pages, you must use Access 2003 to do so.

Deciding Whether to Change Database Formats

Each of the last several versions of Access included a new database format, but upgrading to the newest format was not required. The same is true of Access 2007. The new Access 2007 database format has a new extension, .accdb instead of .mdb. Databases with an .mdb extension open and run normally in Access 2007. If you convert an .mdb database to the new .accdb format, you gain some functionality, but you also lose some things. Whether you should choose to move to the .accdb format depends on your particular database applications and environment.

Reasons to Convert to ACCDB

The .accdb format offers some compelling new features. We very briefly describe a few of those features here; our intent is only to offer some ideas to help you evaluate whether you want to further investigate converting your particular applications.

If you want to use the complex data types Access 2007 provides—attachment, column history, and multivalue fields—you need to use the .accdb format.

On a UI level, the .accdb format adds support for the new Access Themes: Luna (the blue theme used in Microsoft Windows XP) and Obsidian (the black theme used in Windows Vista). In an .accdb database, you see the Access Theme Colors as part of the color picker in the form designer, as shown in Figure 7.

Figure 7. Access Theme Colors in the color picker

Access Theme Colors in the color picker

You can change the Office theme on the Personalize page of the Access Options dialog box. If you select colors from the Access Theme Colors section of the color picker, the user sees colors that comply with the Office theme that is currently in use when the form opens.

What happens if you use the theme colors in an .accdb database, and then save it in .mdb format? If you save an .accdb database back to .mdb format, any colors set from the Access Theme Colors palette are displayed as either blue or black.

When You Should Not Convert to ACCDB

You should not convert a database to the .accdb format if people who are using Access 2003 (or earlier versions of Access) need to open and work with the database. Access 2007 can read and write Access 2000, Access 2002, and Access 2003 .mdb files, but those earlier versions cannot read the .accdb format.

You may also not want to convert if you use Access replication to keep databases synchronized. Replication is not supported by the .accdb format. The tools to create and synchronize masters and replicas are still available in Access 2007 when you are working with an .mdb database. They are located on the Database Tools tab of the Ribbon.

You should not convert your database if it relies on user-level security. As with replication, Access 2007 supports user-level security for .mdb databases, but not for .accdb databases. If your database application relies on user-level security to keep certain users from viewing or editing data or from changing the design of objects, or to change the way different users navigate in your application, you need to redesign the application before you convert to the new format.

New Database Properties

In earlier versions of Access, you could change aspects of the Access application or of the individual database by using the Options dialog box, available on the Tools menu. In Access 2007, application properties, database properties, and startup options are located in the Access Options dialog box, available by clicking the Microsoft Office Button in the upper-left corner of the screen. In addition to the options familiar to you from Access 2003, there are several new options that handle new Access 2007 features. If you work with your existing database in Access 2007, you might find the default options acceptable, or you might want to change the way some of the options work, to make things easier for your end users. Remember also that the end user might change these settings. The following section can help you understand whether and how any such changes will affect your application.

Layout View

Access 2007 introduces a new view for forms and reports named Layout view. Layout view is a cross between Form view and Design view: The form (or report) displays live data, but you can add and delete controls, move controls, and change properties as if you were in Design view. Layout view is useful for developers at design time, but you might not want your users to stumble upon it after your application is deployed. You can turn it off for the entire database by clearing the Enable Layout View for this database check box on the Current Database page of the Access Options dialog box.

Number Display

In earlier versions of Access, displaying large numbers could be problematic. A very large number displayed in a small control in a form or in a report might be truncated. Although it is usually obvious that you are not seeing the whole value, it is a potential cause of confusion. For example, view the Extended Price column on the right side of the Access 2003 datasheet, shown in Figure 8. The column has been adjusted so that you see only part of the extended price.

Figure 8. You cannot always tell whether you are seeing the whole number

You cannot tell if you are seeing the whole number

By default, Access 2007 behaves just as the earlier versions did. But you can change the behavior by selecting the Check for truncated number fields check box in the Current Database page of the Access Options dialog box. If this option is selected, Access displays number signs in place of any number that would be truncated. Figure 9 shows the same datasheet in Access 2007 with this option selected.

Figure 9. Truncated numbers displayed as number signs

Truncated numbers are displayed as number signs

Whether you allow the users of your applications to see the Database window or not, you will probably appreciate the new Navigation Pane for your own use. Unlike the Database window, which could "disappear" behind layers of other windows, the Navigation Pane is always available on the left side of the screen. If you are working with many different objects, you can leave it open all the time. If you want to temporarily hide it, you can press F11 or click the Shutter Bar Open/Close button in the upper-right corner to minimize the space that the Navigation Pane consumes. Repeating the operation returns the Navigation Pane to its previous size.

The Navigation Pane functions much like the Database window in that it displays all the database objects, organized by type. By default, you see all objects at once. You can collapse and expand each group. You can also limit the display to one type at a time (tables, queries, forms, and so on), exactly like the Database window. Figure 10 shows a section of the Navigation Pane when it is set up to show all Access objects in the database, grouped by object type.

Figure 10. Showing all objects at once in a scrollable list

Showing all objects at once in a scrollable list

You can also group by created or modified date, by tables and related views, or by custom groupings that you define. Click the drop-down button at the top of the Navigation Pane to select a different sort option, as shown in Figure 11.

Figure 11. Grouping or filtering objects in the Navigation Pane

Grouping objects in the Navigation Pane

You can define custom groups or add a Search Bar to the Navigation Pane by opening the Current Database page of the Access Options dialog box and clicking Navigation Options. The Navigation Options dialog box is shown in Figure 12. Notice that this dialog box also enables you to show or hide hidden objects and system objects, just as you could in the Access 2003 Database window.

Figure 12. Customizing the look of the Navigation Pane

Customizing the look of the Navigation Pane

If you prefer that the user not see the Navigation Pane at all in your completed application, you can hide it. You can find the option to do this on the Current Database tab of the Access Options dialog box, as shown in Figure 13.

Figure 13. Clearing the check box hides the Navigation Pane at startup

Navigation Pane is hidden at startup

Tabbed Documents

By default, Access 2007 arranges the objects you open just as Access always has, as separate windows. In some applications, users can become lost in the "layers" of windows as they open (but do not always close) forms and reports, as shown in Figure 14.

Figure 14. Objects open as separate windows by default

Objects open as separate windows by default

If you prefer to see a single window with the open objects organized with tabs, you can change the Document Window Options on the Current Database page of the Access Options dialog box, as shown in Figure 15.

Figure 15. Displaying each object in its own window or as a tab in a single window

display each object in its own window or as a tab

In Figure 16, the same objects are open as in Figure 14, but Access represents each object as a tab within a single window. Objects fill the entire window, as if they were maximized within the window containing all the tabs.

Figure 16. Opening objects as tabs in a single window

Open objects as tabs in a single window

Access 2007 treats dialog forms just as earlier versions of Access treated them: When you open a form as a dialog box, Access 2007 displays the form in its own window regardless of the Document Window Options setting.

Visual and User Interface Changes

Access 2007 introduces several new properties to forms that offer a big visual/usability impact with very little effort on your part. If you want to modernize your existing application, consider the Anchor properties, the Split view option, and the new AutoFormat styles.

Designing for an Unpredictable Screen Size

The Tabbed Document window option shows a new version of a problem that developers have long struggled with: How can you make a form use the space available and still look good when you do not know the size of the user's screen? In the past, developers used code that detects the screen resolution when the form runs and readjusts the size of controls programmatically as the form is opened. Access 2007 provides new control properties available at design time that enable Access to size and position controls differently depending on run-time conditions. (Notice that Access 2007 still does not scale font sizes to match the current screen size—it stretches only controls, to maintain a distance from the edge of the form.)

For example, examine the Category form shown (in miniature) in Figure 17. The information in the form takes only about a quarter of the space available in the window.

Figure 17. Screen space not used efficiently

Screen space not used efficiently

The Categories form and its subform, Product List, were probably designed when an 800 × 600 screen was usual and 640 × 480 was not uncommon. Now that such screen resolutions are rare, the forms can use a wider layout. To update the look of this form, we decided that the Product List could be wider, with one line for each row. In Access 2003, a change like that would involve a few minutes with the mouse and the alignment tools. In Access 2007, you can follow this procedure:

  1. In the Navigation Pane, right-click Product List, and then select Design View.

  2. Click a blank area of the form and drag the selection rectangle so that all controls in the form are selected.

  3. On the Ribbon, click Arrange.

  4. In the Control Layout group, click Tabular. The form adjusts, as shown in Figure 18.

    Figure 18. Tabular layout is close to what we want

    Tabular layout is close to what we want

  5. ProductName should be the left-most control. Select it and drag it to the left until the alignment bar is to the left of QuantityPerUnit.

    Notice that when you drop the text box, it is positioned to the left of the QuantityPerUnit control, rather than partially on top of it. Also, the label for the text box moved with it, even though label and text box are in different sections of the form.

    Now complete the new look with the techniques you have always used.

  6. Select all the controls and drag them to the left.

  7. Reduce the space to the right of the last control.

  8. Select only the controls in the Detail section and drag them to the top of the section.

  9. Reduce the space in the Detail section under the controls.

  10. Reduce the space in the Form Footer section.

  11. The Product Name label should be left-aligned. Select the Product Name label and then, on the Ribbon, click Design.

  12. On the Ribbon, in the Font group, click Align Left.

    The new layout looks like Figure 19.

    Figure 19. You can use all the layout and alignment tools you are used to

    Using all the layout and alignment tools

When you save the Product List changes and open the Categories form, the subform takes the same amount of space as before. To make it fill the space to the right and below its starting location, follow these steps:

  1. Right-click the Categories form, and then select Design View.

  2. Select and right-click the Product List subform, point to Anchoring, and then select Stretch Down and Across, as shown in Figure 20. (If you examine the subform's Properties window, you see that the Stretch Down and Across option sets both the Horizontal Anchor and Vertical Anchor properties of the subform to Both.)

    Figure 20. Anchoring options set anchoring properties for the control

    Anchoring options set properties for the control

  3. Switch to Form view. The subform now stretches down and across whatever screen space is available, as shown in Figure 21.

    Figure 21. Subform filling available space

    The subform now fills available space

You can experiment with displaying and resizing the Navigation Pane to see the subform control resize automatically. You might also want to change anchoring properties for the other controls on the form to distribute them across the available space. To move the picture to the right side of the form and expand the Category Name and Description controls, follow these steps:

  1. Switch the Categories form back to Design view.

  2. Select the Picture control.

  3. Change the Horizontal Anchor property to Right.

  4. Select the CategoryName control and the Description control (but not their labels).

  5. Change the Horizontal Anchor property to Both.

  6. Switch to Form view. The controls now fill the available space, as shown in Figure 22.

    Figure 22. Anchoring properties set so that the controls fill available space

    Anchoring properties set so controls fill space

AutoFormat

The AutoFormats available in Access have not changed in several years, and that International theme (the one with the globe graphic) has been used over and over. Access 2007 introduces twenty-five new AutoFormat themes that you can use to update your forms and make them consistent across the application.

You can find AutoFormat on the Ribbon, under Layout in the Form designer or under Layout in the Report designer, as shown in Figure 23.

Figure 23. New AutoFormats in Access 2007

Access 2007 provides many new AutoFormats

Split View

Access 2003 includes several possible ways to view data in a form: Single, Continuous, Datasheet, PivotTable, and PivotChart. Access 2007 also adds one more view: Split Form. (All these views are allowed values for the form's Default View property.) The new Split Form view provides a feature that offers big impact for the user with little work for the developer.

Split Form view, as the name implies, means that your form is split into two parts: One part is the same as Form view, and the other part looks like Datasheet view. Figure 24 shows the Orders form as a split form.

Figure 24. Split Form view combines Form view and Datasheet view

Split Form view combines Form and Datasheet views

Data in the two parts of the Split Form view is synchronized: If you move from one row to another in the datasheet, the form moves to the row you selected in the datasheet. If you move from one field to another in the form, the datasheet's focus moves to the corresponding column.

To provide Split Form features for a form in your database, open the form in Design view and find the Default View property in the Properties Pane. Change the Default View property to Split Form. There are several other new properties that enable you to further customize your split form. For example, you can choose whether the datasheet should be editable, determine the orientation of the datasheet relative to the form, or decide whether the user can resize the two parts with the splitter bar.

Replacing Code and Third-Party Controls

As an Access developer, you have probably created a standard set of tools and techniques that you use from one project to the next. When the Access 2007 design team put together the list of features that would be added to Access 2007, they looked at the kinds of code and ActiveX controls that developers were using repeatedly. When you move up to Access 2007, you can eliminate tools you used for rich text, printing to PDF, and choosing a date. You can also delete code for alternating row shading on a report or form or for enabling the user to dynamically edit lookup data. Access 2007 also adds a new way of handling global variables, the TempVars collection.

You might want to ignore these new features until you design your first Access 2007 database; it is usually best to leave working code unchanged in a deployed application. But if all your users are moving to Access 2007 and you will be maintaining the application for some time to come, now might be the perfect time to eliminate code and controls that are no longer needed.

Saving Rich Text in a Field

In an Access 2007 Text field or Memo field, you have the option of saving unformatted text (as you always have in Access) or saving rich text—text that contains formatting such as bold and italic. To store rich text, you need to use a Text Box control on a form with its Text Format property set to Rich Text, as shown in Figure 25.

Figure 25. Setting the Text Format property to Rich Text

Setting the Text Format property to Rich Text

When you edit data by using the form, the font tools in the Ribbon are enabled when you are in the Rich Text text box. You can use them to add bold, italic, or underlined formatting to text, to change the color of the font, or to add highlighting, as shown in Figure 26.

Figure 26. Rich Text enables you to add bold, italic, and other formatting

Rich Text enables you to add formatting

Behind the scenes, Access stores the formatted text as HTML, as shown in Figure 27.

Figure 27. Internally, rich text is stored as HTML

Internally, rich text is stored as HTML

Note

Because Access 2007 stores its rich text in HTML format, instead of the Rich Text Format (RTF) format used by several third-party controls, you cannot just replace an ActiveX control with an Access 2007 control that provides similar functionality. If you are currently using a third-party control, and you have already stored RTF data in your database, you should continue using the existing control. If you are newly adding support for rich text to your application, however, use the new text box option instead.

Sending a Report to PDF

The Portable Document Format (PDF) is popular for distributing documents. Many developers have purchased and installed software that enables them to create PDF files from Access reports and other Office system documents. With Access 2007, you no longer need to purchase extra software to print to PDF format.

NoteNote

To print to PDF, you must download and install the PDF DLLs. On first use, the application prompts you with the download information.

If you are previewing a report and you want to send it to a PDF, on the Ribbon, in the Data group, you can select PDF orXPS, as shown in Figure 28.

Figure 28. Saving a report in PDF format

Saving a report in PDF format

Note

XPS format is the Microsoft version of a distributable document format. It is currently not as widely used as is PDF, but its intent is similar.

You are prompted for a name and other options, as shown in Figure 29.

Figure 29. Specifying different sizes and whether to open the file

Specifying different sizes

The Options button in the dialog box enables you to specify pages to print and whether to include document structure tags. After you click Publish, Access 2007 creates the PDF document. Then you see another dialog box, shown in Figure 30, that enables you to save the export steps so you do not have to complete the wizard the next time you save the same report in PDF.

Figure 30. Saving the export steps

Saving the export steps

You do not need to preview the report before you send it to PDF. In the Navigation Pane, you can just right-click the report, point to Export, and then click PDF or XPS.

You can use the OutputTo method to export to a report to PDF in Visual Basic code. To do this, set the OutputFormat argument to acFormatPDF. If you would rather attach a PDF document to an e-mail message, you can use the SendObject method.

Working with the DatePicker Control

If your database requires the user to enter dates, most developers provide a popup calendar, usually in the form of an ActiveX control. In Access 2007, you no longer need a separate control or popup form to help the user choose a date. In fact, you really do not need to do anything special at all.

By default, when the user moves to a Date/Time field in a datasheet or to a text box that is bound to a Date/Time field, a small calendar icon appears next to the text box, as shown in Figure 31.

Figure 31. A cursor in a text box control formatted as a date displays a DatePicker control

DatePicker control

The user can click the icon to see a calendar, as shown in Figure 32.

Figure 32. Click the DatePicker to see a calendar and select a date

Click the DatePicker to select a date

If you do not want the user to see this DatePicker control, you can turn it off in the field properties in the Table designer. Each Date/Time field has a Show DatePicker property, as shown in Figure 33.

Figure 33. Turning off the DatePicker for a particular field

Turning off the DatePicker for a particular field

You can change the value of the Show DatePicker field from For dates to Never if you prefer not to see the DatePicker in datasheets or forms. If you want to keep the property setting in the table but change it for the current form, you can turn it off at the form level also: The Text Box control has a Show DatePicker property.

Alternating Row Shading on a Report

Long tabular reports are easier to read if every other row is shaded slightly. In the past, Access developers have done this by writing a bit of code for the Detail section's Format event, switching the BackColor property of the section. In Access 2007, you can do the same thing by changing one property of the Detail section, the Alternate Back Color property, shown in Figure 34.

Figure 34. Changing the Alternate Back Color property creates a "striped" report

Changing the Alternate Back Color property

Use the Color Picker to choose an appropriate color for Alternate Back Color. Obviously, this needs to be a different color than the BackColor property. Preview the report to see the alternate shading effect.

Note that alternate row shading is not limited to reports; the Detail section of a form also has an Alternate Back Color setting. Figure 35 shows the Product List form with alternate shading.

Figure 35. Forms can use alternate shading just as reports do

Forms can use alternate shading just as reports do

Using a "Not in List" Form

Most of the time, combo box controls are based on static lists: You enter the values once and they rarely change. Sometimes you want the user to be able to add new values to a lookup table as they are adding data to the main tables. In earlier versions of Access, you could do this by adding code to the NotInList event of the Combo Box object, opening a lookup form or otherwise prompting the user to add the new value to the lookup table.

In Access 2007, you still need to create a form to edit the lookup table, but you do not need to write any code. Instead, edit the List Items Edit Form property of the Combo Box object to reference the name of the lookup table's form, as shown in Figure 36.

Figure 36. Specify a form name in the List Items Edit Form property

Specify a form name

Later, when the user enters a value in the combo box that does not already exist in the lookup table, Access 2007 prompts the user to edit the items in the list, as shown in Figure 37.

Figure 37. User is prompted if a name is not in the list

User is prompted if a name is not in the list

If the user chooses to edit by clicking Yes, Access opens the form you specified as a modal form, as shown in Figure 38.

Figure 38. Other tabs disabled when you are editing list items

Other tabs disabled when you are editing list item

After the user closes the form, Access refreshes the list in the combo box—the user can move on to the next field with no further prompts.

Button Enhancements

The Button control is one of the most commonly used controls in Access. In earlier versions, buttons were also one of the most limited in terms of their functionality.

For example, you could add a picture to your button, but displaying a picture and a caption was out of the question. Did you want to change the pointer when the user paused it over the button? Write some Visual Basic code that calls the Microsoft Windows API. Transparency? Forget about it. Align a button's caption? I hope a centered caption was your preference. The Access 2007 Button control includes several new properties that address these scenarios.

The Picture Caption Arrangement property enables you to display a caption along with the picture that you added to your button. Setting the Picture Caption Arrangement property to a value other than the default value of No Picture Caption tells Access to display the contents of the button's Caption property.

The Picture Caption Arrangement value that you select aligns the caption in relation to the picture, but it does not set the overall alignment of the picture and caption. For example, setting a button's Picture Caption Arrangement property to Left while setting the button's Alignment property to Right displays the caption to the left of the picture while aligning both to the right side of the button.

The Cursor On Hover property enables you to easily change the pointer that is displayed when the user hovers over a button. The possible Cursor On Hover settings are Default and Hyperlink hand. Setting the Cursor On Hover property to Hyperlink hand tells Access to display the familiar pointing hand that appears when hovering over a hyperlink.

NoteNote

The Cursor On Hover property does not use the Link Select setting in the Mouse Properties Control Panel.

Setting the Transparent property to Yes makes a button invisible, but still functional, to the user. For example, you could place several transparent buttons over a picture displayed in an image control and run various macros or Visual Basic event procedures depending on which part of the picture the user clicks.

The Button control also has added the BackStyle property, which was available for some of the other controls in earlier versions of Access. Setting the BackStyle property of a button to Transparent only does not hide the button's caption or picture.

The Alignment property enables you to align a button's caption or picture.

TempVars: A New Developer Tool

In the past, global variables have been convenient for setting information at startup and then using it throughout the application. But they have also been problematic. Their least convenient "feature" is that Access clears the value of all global variables if your application triggers an error.

Access 2007 includes a collection named TempVars for storing global variables. Its main intent is to enable power users who work with Access macros to store and retrieve values, other than by using a text box on a hidden form. You can use macro actions to set and retrieve values from the collection.

TempVars has some features that benefit developers in addition to power users:

  • Access does not clear the collection when an error occurs.

  • Because it is a collection, you can iterate through it to find all assigned global values at any given time.

The TempVars collection makes it simple to add or modify a value—just set the value. If it does not already exist, Access adds it to the collection for you.

TempVars("varDBName").Value = CurrentDb.Name

After you have created a TempVars value, you can read and use the value in any procedure (or Access macro) in the database.

Me.Caption = TempVars("varDBName").Value

You can also iterate through the collection to see all global values.

Public Sub DisplayTempVars()
    Dim tv As Variant
    For Each tv In TempVars
        Debug.Print tv.Name & " = " & tv.Value
    Next tv
End 

Data Handling Changes

As you migrate applications to Access 2007, you will find several new features involving data handling, and most of these new features require the new ACCDB database format. Although there are many new data-related features, this article focuses on two specific features that you should consider as you migrate your applications: The first, which is available in the ACCDB format, involves how images are saved in Access 2007. The second feature, available in either MDB or ACCDB format, deals with referring to images that are stored in the file system rather than embedded in the database itself.

Saving Images in Their Native Format

In earlier versions of Access, any image you added to a form or report in an Image control was stored in the database as a bitmap (BMP). BMP image files are not compressed, as are images stored in JPEG, GIF, and other image file formats. Therefore, databases could become larger than they needed to be. Also, features such as transparency that are offered by other formats are lost in the conversion.

Access 2007 stores images (that is, the image specified as the Picture property of an Image control) in their native format with no conversion.

Preserve Native Image Format Property

By default, MDB databases continue to store images in BMP format to preserve cross-version compatibility. If you know that only Access 2007 client applications will access your database, and you want to take advantage of the new image storage format, you can change the Picture Property Storage Format by following these steps:

  1. Click the Microsoft Office Button, and then select Access Options.

  2. Select Current Database, and then select Preserve source image format (smaller file size).

Working with the Attachment Field Type

The Northwind Traders application has always included pictures of each employee. The Access 2000 version stores those pictures in the same folder as the database, rather than embedding them as OLE Object fields in the Employees table. VBA code runs on the Employee form's Current event to display each picture as the user moves through the form.

In Access 2007, this code can be eliminated by using one of the new field types, the Attachment type. Attachment enables you to link one or more files to the current record.

To take advantage of the Attachment field type, you must start by converting the database to the ACCDB format. You can do that by selecting Convert on the Microsoft Office Button menu.

After you convert the database, you can follow these steps to add and use an Attachment field in place of the current Text field used to store the name of each photo image:

  1. In the Navigation Pane, right-click the Employees table, and then select Design View.

  2. Add a new field named PhotoAttach, as shown in Figure 39.

    Figure 39. Add a new field for the attachment

    You must add a new field for the attachment

  3. Switch to Datasheet view.

  4. Move to the PhotoAttach field and double-click the paperclip icon for the first record.

  5. In the Attachments dialog box, attach the file EMPID1.bmp, found in the same folder as the original database.

  6. Repeat the process for the other rows. This table is small enough that it takes only a small period of time to add all attachments. You could write code to automate the process if you need to do this for a larger table.

  7. In the Navigation Pane, right-click the Employees form.

  8. Select Design View.

  9. Delete the OLE Object and other controls currently used to display the file referenced in the Photo field.

  10. Press Alt+F11 to open the Visual Basic Editor.

  11. Find and comment out (or delete) the Form_Current procedure.

  12. On the Ribbon, in the Tools group, click Add Existing Field.

  13. In the Field List, select PhotoAttach and drag it to the form.

  14. Switch to Form view and page through the form to see the photo change.

Conclusion

A full discussion of each new feature in Access 2007 is beyond the scope of this article; the goal here is to introduce you to the features that affect you immediately, as you migrate your existing applications to Access 2007. We are very excited about the new features addressed here—it seems that the Access product team has looked at the kinds of issues facing Access developers, and have made significant strides to reduce the amount of effort it takes to accomplish common tasks. Just being able to anchor and dock controls makes a big difference in the "professionalism" of your finished forms. Add that to the "not in list" handling, native support for image formats, and new AutoFormats, and we think you will really benefit from the new Access 2007 features.

About the Authors

Jan Fransen is a writer, trainer, and consultant specializing in Microsoft products. As a writer, Jan has developed training courseware for AppDev, contributed to books about Microsoft Office applications, written white papers for publication on MSDN, and created samples designed to help developers get up to speed quickly on new Microsoft products and features.

Ken Getz is a senior consultant with MCW Technologies and a courseware author for AppDev. He is coauthor of ASP.NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2002), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).

Additional Resources

To learn more about the products and technologies mentioned or used in this article, see these resources: