Jan FransenA23 Consulting

Ken GetzMCW Technologies

June 2006

Applies to: Microsoft Access 2007

Introduction

By now, you’ve almost certainly read about, seen, or experimented with the much-vaunted Office 2007 user interface. The features you hear about most often, like the Ribbon and the Quick Access Toolbar, are aimed at making the Office products more approachable for interactive users. You may know that the Access team has also added a number of features aimed particularly at developers. You can see examples of many of these new features in the sample databases and templates that ship with Access 2007, all of which were designed from the ground up with Access 2007 in mind. What you may not be sure of is how (or whether) an application you developed for a previous version of Access is going to look and work when you first open it in Access 2007. You may also be wondering whether you can make use of these new features without needing to completely revamp your applications.

In this paper we’ll answer some of the questions you might have about your existing applications:

  • Features you (and your users) will notice when opening an application written in Access 2007 that was written in a previous version of Access.
  • When to convert your existing Access database to the new Access 2007 format (and when not to).
  • How you can leverage new Access 2007 features to improve the visual appeal and user interface of your existing application.
  • Changes you can make to eliminate unnecessary VBA code or ActiveX controls from your application.

The focus of this article is to demonstrate how you can make best use of some of the new features in Access 2007 without rewriting your existing application. Along the way, however, we’ll 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’ll be using the familiar Northwind sample database, in its Access 2000 incarnation. This version shipped with Access 2000, Access 2002, and Access 2003 (but not with Access 2007, in which it has finally been replaced with a version that doesn’t look as if it was created in 1992).

Opening Your Access 2000/2002/2003 Application in Access 2007

You can open applications written in a previous version of Access in Access 2007, and the applications should work just as they did in Access 2003, with a few exceptions. In addition to the obvious user interface changes such as the Navigation Pane and the Ribbon, you’ll see different security messages from Access 2003 and changes to how Access displays custom menus. You’ll also find that Access 2007 has dropped support for one of the Access objects, the data access page.

Security Changes

As you opened your application in Access 2003, you may have seen 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 won’t see any dialog boxes when you open a database. Instead, if you open an unsigned database you’ll see, at most, a security alert like the one shown in Figure 1. The alert is displayed under the Ribbon in an area called the Business Bar.

Figure 1. A security alert replaces the multiple modal dialog boxes you encountered in Access 2003.

Like the original Security Warning dialog box, whether or not you see the security alert depends on your macro settings. In Access 2007, you can change your macro settings by clicking the new Office button (see Figure 2) in the top left corner of the screen and then clicking the Access Options button at the bottom of the menu.

Figure 2. The new Office button gives you access to file-related actions as well as options that apply to your Access installation, regardless of which database you’re using.

In the Access Options dialog box, choose the Trust Center tab and click Trust Center Settings. The Trust Center dialog box opens. Choose the Macro Settings tab. You’ll see the choices shown in Figure 3.

Figure 3. You can choose whether to run macros, and whether you will receive notification if macros are disabled. Note: Just as in previous versions of Access, the word macro in the context of Office security refers to VBA code, not Access macros.

In general, developers will probably find the Disable all macros with notification option the most useful choice, particularly because the security alert that Access 2007 displays provides two quick and easy ways to enable code once the database is loaded. To enable code for the current session only, click the Enable Content button next to the security alert message. You’ll see the Trust in Office dialog box shown in Figure 4. Select Enable the content and click OK to enable the code for this session only.

Figure 4. The Trust in Office dialog box allows you to temporarily enable content.

If you’d like to determine what happens when you load this database (or any database) and persist the settings, open the Trust Center dialog box by clicking the Trust Center button 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’re 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 still 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, IsTrusted, so that you can check whether the rest of your application will be allowed to load. You could 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 something like Figure 5.

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

Custom Menu Bars

In previous versions of Access, you can create your own custom menu bars, and can even replace the default menu bar with a custom menu bar like the one shown in Figure 6.

Figure 6. In Access 2003 and before, you can define a custom menu using Access macros and the Customize dialog box.

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 don’t disable the built-in toolbars, Access adds a new tab to the Ribbon named Add-Ins. Your custom menu appears within the Add-Ins tab, as shown in Figure 7.

Figure 7. The Add-Ins tab displays custom menus.

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

Data Access Pages

In prior versions of Access, data access pages provided a means for you to develop browser-based pages that displayed Access data. If your application contains data access pages, you’ll still see them in the Navigation Pane, but Access 2007 no longer provides a Data Access Page designer. If you open a data access page from Access 2007, Access launches an instance of Internet Explorer and opens the data access page in that instance. If you need to change the design of your pages, you’ll need to use Access 2003 to do it.

Deciding Whether to Change Database Formats

Each of the last several versions of Access has also included a new database format, but upgrading to the newest format has not been required. The same is true of Access 2007. This time the new database format comes with a new extension, ACCDB instead of MDB. MDB databases open and run normally in Access 2007; if you convert an MDB database to the new ACCDB format, you will gain some functionality, but you’ll also lose some things. Whether you choose to move to the ACCDB format or not depends very much on your particular database applications and environment.

Reasons to Convert to ACCDB

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

If you want to use the complex data types Access 2007 provides-attachment, column history, and multi-value fields-you’ll need to use the ACCDB format.

Figure 8. In an ACCDB database, you see Access Theme Colors in the color picker.

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

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

What happens if you use the theme colors in an ACCDB, and then save it in MDB format? MDB databases don’t show the Access Theme Colors section in the color picker. If you save an ACCDB database back to MDB format, any colors set from the Access Theme Colors palette appear as either blue or black.

When Not to Convert to ACCDB

You should not convert a database to the ACCDB format if people using Access 2003 or earlier need to open and work with the database. Although Access 2007 can read and write Access 2000/2002/2003 MDB files, those earlier versions cannot read the ACCDB format.

You should also think twice about converting 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’re working with an MDB database. You’ll find them on the ribbon’s Database Tools tab.

Finally, you shouldn’t convert your database if your database relies on user-level security. As with replication, Access 2007 supports user-level security for MDB databases, but not for ACCDB databases. This obviously isn’t an issue for databases that don’t implement user-level security to begin with, but 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’ll need to redesign the application before converting to the new format.

New Database Properties

In previous versions of Access, you could change aspects of the Access application or of the individual database using the Options dialog box, available through the Tools|Options menu selection. In Access 2007, you’ll find application and database properties as well as startup options in the Access Options dialog box, available from the Office button in the top left corner of the screen. In addition to the options familiar to you from Access 2003, you’ll find several new options that handle new Access 2007 features. When you work with your existing database in Access 2007, you may find the default options perfectly acceptable. But you might want change the way some of them work for your benefit, or to make things easier for your end users. Keep in mind, too, that the end user might change these settings. You will want to be aware of 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 around, and change properties as though you were in Design view. Layout view is handy 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 de-selecting the Enable Layout View for this database check box on the Current Database page of the Access Options dialog box, as shown in Figure 9.

Figure 09. You can restrict access to Layout view for the database.

Number Display

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

Figure 10. You can’t always tell if you’re seeing the whole number.

By default, Access 2007 behaves just as the previous versions did. You can change the behavior, though, by selecting the Check for truncated number fields option in the Current Database page of the Access Options dialog box. With the option set, Access will display pound signs in place of any number if the number would be truncated. Figure 11 shows the same datasheet in Access 2007 with the option set.

Figure 11.Truncated numbers are displayed as pound signs.

Navigation Pane vs. Database Window

Whether you allow the users of your applications to see the Database window or not, you’ll 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’re 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 real estate the Navigation Pane consumes, as shown in Figure 12.

Figure 12.Figure 12. You can close the Navigation Pane to a narrow bar when you need the real estate for something else.

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 of the database objects, organized by type. By default, you’ll 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 13 shows a portion of the Navigation Pane when it is set up to show all Access objects in the database, grouped by object type.

Figure 13.You can show all objects at once in a scrollable list.

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

Figure 14. You can group or filter 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 the Navigation Options button. The Navigation Options dialog box is shown in Figure 15. Notice that this dialog box also allows you to show or hide Hidden or System objects, just as you could in the Access 2003 Database window.

Figure 15.You can customize the look of the Navigation Pane.

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

Figure 16. Clearing the checkbox means that the Navigation Pane will be 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 get lost in the “layers” of windows as they open, but don’t always close, forms and reports, as illustrated by Figure 17.

Figure 17. Objects open as separate windows by default.

If you’d 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 18.

Figure 18.You can choose to display each object in its own window or as a tab in a single window.

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

Figure 19.You can choose to open objects as tabs in a single window.

Access 2007 treats dialog forms just like previous versions of Access: 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’re looking to modernize your existing application, take a look at the Anchor properties, the Split view option, and the new AutoFormat styles.

Designing for an Unpredictable Screen Size

The Tabbed Document window option illustrates 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 don’t know the size of the user’s screen? In the past, developers have used code that detects the screen resolution when the form runs and readjusts the size of controls programmatically as the form is opened. Now Access 2007 provides new control properties available at design time that allow Access to size and position controls differently depending on runtime conditions. (Note that Access still doesn’t scale font sizes to match the current screen size-it will only stretch controls to maintain a distance from the edge of the form.)

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

Figure 20. Screen real estate isn’t being used efficiently.

The Categories form and its subform, Product List, were probably designed when an 800 x 600 screen was the norm and 640 x 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 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. Right-click Product List in the Navigation Pane and choose Design View.
  2. Click on a blank area of the form and drag the selection rectangle so that all controls in the form are selected.
  3. In the ribbon, click Layout.
  4. In the Control Layout chunk, click Tabular. The form adjusts as shown in Figure 21.
  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. Note that when you drop the text box, it’s positioned to the left of the QuantityPerUnit control, rather than partially on top of it. Note also that the text box’s label 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’ve always used:.

  1. Select all of the controls and drag them to the left.
  2. Reduce the space to the right of the last control.
  3. Select only the controls in the Detail section and drag them to the top of the section.
  4. Reduce the space in the Detail section under the controls.
  5. Reduce the space in the Form Footer section.
  6. The Product Name label should be left-aligned. Select it and click Design on the ribbon.
  7. Click the Align Left button in the Font chunk of the ribbon. The new layout looks like Figure 22.
Figure 21. The Tabular layout is close to what we want.
Figure 22. You can still use all the layout and alignment tools you’re used to.

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 choose Design View.
  2. Select and right-click the Product List subform and choose Stretch Down and Across from the Anchoring menu, as shown in Figure 23. (If you look at the subform’s Properties window, you’ll see that the Stretch Down and Across choice sets both the Horizontal Anchor and Vertical Anchor properties of the subform to Both.)
  3. Switch to Form view. The subform now stretches down and across whatever screen space is available, as shown in Figure 24.
Figure 23.The Anchoring menu choices set Anchoring properties for the control.
Figure 24. The subform now fills available space.

You can experiment with displaying and resizing the Navigation Pane to see the subform control resize automatically.

You may 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 and Description controls (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 25.
Figure 25.The Anchoring properties are now set so that the controls fill whatever space is available.

AutoFormat

The AutoFormats available in Access haven’t changed in a number of years, and you’ve probably seen enough forms with that International theme (the one with the globe graphic) to last the rest of your life. Access 2007 introduces twenty-five new AutoFormat themes 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 the Report designer, as shown in Figure 26.

Figure 26. 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. In Access 2007, PivotTable and PivotChart are more dynamic-you can view any form as a PivotTable or PivotChart whether it was designed as one or not. Access 2007 also adds one more view: Split Form. (All of 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 27 shows the Orders form as a split form.

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

Data in the two parts of the Split 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 a number of other new properties that allow you to further customize your Split Form. For example, you can choose whether or not the datasheet should be editable, determine the orientation of the datasheet relative to the form, or decide whether the user will be able to resize the two parts with the Splitter bar.

Replacing Code and Third-Party Controls

As an Access developer, you’ve 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. You’ll find that 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 allowing the user to dynamically edit lookup data. Finally, Access 2007 adds a new way of handling global variables, the TempVars collection.

You may want to ignore these new features until you design your first Access 2007 database; it’s usually best to leave working code alone in a deployed application. But if all your users are moving to Access 2007 and you’ll 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 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 italics. 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 28.

Figure 28. You can se the Text Format property to Rich Text.

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

Figure 29. Rich Text allows you to add bold, italic, and other formatting.

Behind the scenes, Access stores the formatted text as HTML. You’ll see the HTML tags if you look at the field in a datasheet or query rather than through a form, as shown in Figure 30.

Figure 30.Internally, rich text is stored as HTML. Note:Because the Access 2007 stores its rich text in HTML format, as opposed to the RTF (Rich Text Format) format used by several third-party controls, you won’t be able to simply replace an ActiveX control that provides similar functionality. If you’re currently using a third-party control, and you’ve already stored RTF data in your database, you’ll want to continue using the existing control. If you’re 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) format has become popular for distributing documents. Many developers have purchased and installed software that allows creating PDF files from Access reports and other Office documents. With Access 2007, you no longer need extra software to print to PDF format.

If you’re previewing a report and would like to send it to a PDF, you can choose PDF or XPS from the Ribbon’s Data chunk, shown in Figure 31.

Figure 31. You can save a report in PDF format. Note:XPS format is Microsoft’s version of a distributable document format. It’s not currently as widely used as is PDF format, but its intent is similar.

You’ll be prompted for a name and other options, as shown in Figure 32.

Figure 32. You can specify different sizes, and whether to open the file.

The Options button in the dialog box allows you to specify pages to print and whether to include document structure tags. Once you click the Publish button, Access 2007 creates the PDF document. You’ll see another dialog box like Figure 33 that allows you to save the export steps so you don’t have to go through the wizard next time you save the same report in PDF format.

Figure 33. If you’ll be printing the report to PDF often, you can save the export steps.

You don’t need to preview the report before sending it to PDF. You can just right-click the report in the Navigation Pane and choose Export, then PDF or XPS from the menu.

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 don’t need to do anything special at all.

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

Figure 34. If the cursor is in a text box control formatted as a date, you’ll see a DatePicker control.

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

Figure 35. You can click the DatePicker to see a calendar and choose a date.

If you don’t 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 Date Picker property, as shown in Figure 36.

Figure 36. You can turn off the DatePicker for a particular field.

You can change the value of Show Date Picker 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 as well: The Text Box control has a Show Date Picker 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 accomplished this by writing a little bit of code for the Detail section’s Format event, toggling the BackColor property of the section. In Access 2007, you can accomplish the same thing by changing one property of the Detail section, the Alternate Back Color, shown in Figure 37.

Figure 37.Changing the Alternate Back Color creates a “striped” report.

Use the Color Picker to choose an appropriate color for Alternate Back Color. Obviously, this needs to be a different color than the Back Color. Preview the report, and you’ll see the alternate shading effect as shown in Figure 38.

Figure 38. Tabular reports are easier to read if every other row is shaded.

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

Figure 39.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’re adding data to the main tables. In past versions of Access, you could accomplish this by adding code to the Combo Box object’s NotInList event, 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 don’t need to write any code. Instead, edit the List Items Edit Form property of the Combo Box to reference the name of the lookup table’s form, as shown in Figure 40.

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

Later, when the user enters a value in the combo box that doesn’t already exist in the lookup table, Access 2007 prompts the user to edit the items in the list, as shown in Figure 41.

Figure 41. If you enter a name that’s not in the list, you’ll be prompted.

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

Figure 42.When you’re editing the list items, other tabs are disabled.

Once 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.

In the past, global variables have been convenient for setting information at startup and then using it throughout the application. But they’ve 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 was to provide power users who work with Access macros with some means for storing and retrieving values, other than a text box on a hidden form. You can use macro actions to set and retrieve values from the collection.

TempVars has a couple of features that benefit for developers as well as power users:

  • Access doesn’t clear the collection when an error occurs.
  • Because it’s 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 doesn’t already exist, Access adds it to the collection for you:

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

Once you’ve created a TempVar 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’ll find several new features involving data handling, and most of these new features require the new ACCDB database format. Although there are lots of 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 either MDB or ACCDB format, involves how images are saved in Access 2007. The second feature, available only in ACCDB, 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 previous 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 aren’t 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 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.

Figure 43.Pictures are stored in their native format.

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 embedded 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 allows 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 from the Office button’s menu.

Once you have converted 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. Right-click the Employees table in the Navigation Pane and choose Design View.
  2. Add a new field named PhotoAttach, as shown in Figure 44.
  3. Switch to Datasheet view.
  4. Move to the PhotoAttach field and double-click the paperclip for the first record.
  5. Use the Attachments dialog box to add an attachment to 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 will only take a minute 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, find and right-click the Employees form.
  8. Choose 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 VB Editor.
  11. Find and comment out (or delete) the Form_Current procedure.
  12. Click Add Existing Field in the Ribbon’s Tools chunk.
  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.
Figure 42. When you’re editing the list items, other tabs are disabled.

Conclusion

A full discussion of each of Access 2007’s new features is beyond the scope of this paper; the goal here was to introduce you to the features that will affect you immediately, as you attempt to migrate your existing applications to Access 2007. We’re very excited about the new features covered here-it seems that the Access designers have 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. Simply being able to anchor and dock controls makes a huge difference in the “professionalism” of your finished forms. Add that to the “not in list” handling, native support for image formats, and (finally) new AutoFormats, and we think you’ll really benefit from the new Access 2007 features.

About the Author

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 on Microsoft Office, 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).