Microsoft Access Products from FMSTop 14 New Features of Microsoft Access That Aren't Available in Access 2003 or Earlier

Provided by Luke Chung, President of FMS, Inc.

Note: This paper was originally written for Access 2007, but also applies to Access 2016, 2013 and 2010 if you're migrating from Access 2003 or earlier.

If you're still using Microsoft Access 2003, MS Access 2007, 2010, 2013, 2016 will let you create applications with features that were impossible or cumbersome to provide in prior versions.

As power users and MS Access developers, it's critical that we understand the new features and how they give ourselves and our users advances at little to no cost. Hopefully, you'll find it easy to justify migrating to Access 2007/2010/2013/2016. After all, people don't want change just for technology's sake. They want some real productivity gains.

In this paper, we outline some of the top features we enjoy from Access 2007/2010/2013/2016 that couldn't be easily done in Access 2003. But before we start, here are some reasons why the transition feels painful.


There are some issues with migrating from Access 2003:

  • Complaints about the Access/Office ribbon and how difficult it is to find things is universal. It is initially confusing to find features I knew in the menus but couldn't find on the ribbons. It was a real productivity drain and very frustrating. But over time, I understood the philosophy behind the new organization and eventually found what I needed.
  • Complaints about the database container replaced by the Navigation Pane is also common. There are definitely lost features with the new Navigation Pane that I dislike:
    • Not being able to see a list of object sorted by modified date the way you'd expect in Windows Explorer.
    • Losing the one-click Alt+D to put the current object in design mode
    • Losing the toolbar item to automatically jump to a form or report's module code
    • Missing right mouse click items like import/export
  • Complaints about losing custom command bars (toolbars) with the new ribbons. Yup, this is a significant loss. No arguments here.
  • Complaints about losing the Windows menu to select from the list of opened objects. Yup, that's gone too.
  • Many features were deprecated in Access 2013 including support for ADPs, pivot tables and pivot charts. If these are issues, stick with Access 2010. See our Microsoft Access Versions page for more details.

Although there are some changes I would have preferred not to see, there are many more steps forwards than backwards. And in many cases, the backwards steps required retraining myself, while the forward steps improve the users of the Access databases. That's a price I'm willing to pay.

Why do we bother to create applications on Microsoft Access instead of another platform like .NET, Java, or Visual Basic 6?

One of the important concepts for using MS Access is that our applications take advantage of the features of the underlying Access product. While we customize the tables, queries, forms, reports, code, etc. to create our solution, we don't worry about all the features Access provides.

Therefore, when Microsoft upgrades Access, all our Access databases inherit those new features at little to no cost. We do ourselves and our users and customers a disservice when we have the opportunity to provide these new features but don't.

Over time, our Access databases often represent investments of $10K, $100K, or $1M+, so adding significant new features by moving to a new platform, is a very low cost investment for a very valuable solution. Features that were previously too expensive or impossible, all of a sudden become cost effective. The result is a tremendous boost in functionality and productivity by upgrading.

Here are some things that I've found valuable which are very easy to do in Access now that were nearly impossible to do in earlier versions.


#1: Report View Eliminates Extra Reports

The new Report View feature (unlike Print Preview) lets users perform ad hoc filters on a report similar to the way they can filter forms. For instance, you can filter on a specific column value or words that begin with or contain certain letters, a date range, etc.

The result is the report showing what the user wants to see, with summaries automatically recalculated. This requires no programming on your behalf, just opening the report in this new format rather than Print Preview.

Report View has some limitations such as its lack of support for code that adjusts displays and calculations in the Detail event, but for most reports, this works great and eliminates the need to create a bunch of individual reports for users or an ad hoc data filter system. This empowers end users and reduces the developer's work.

Using Report View in Microsoft Access


#2: Add Datasheet Totals

Easily add a total row to sum or count numeric fields in datasheets. The summary automatically respects any filter on the list and appears exactly where users expect.

Simply click on the Totals item on the Records ribbon. That creates a Total row in your datasheet where you can specify the type of summary you want for each column:

Totals on datasheets in Microsoft Access
Add a Summary Row to Datasheets

Prior to this, you could show a subform's totals in the parent form by referencing a calculated summary control in the subform's footer, but it wouldn't be right in the column of the datasheet. The Totals data sticks with the column so if the column is resized or moved, it remains with it. This feature also works for datasheet views of tables, queries, and forms.


#3: Output Reports to PDF

PDF output significantly extends Access applications. While there were ways to do this before by adding a PDF printer, having it integrated as another output type is a great addition and makes it easy to distribute your reports via email.

In fact, our Total Access Emailer product for Access 2007 and later, takes advantage of this feature to let you email filtered Microsoft Access reports in PDF format to each recipient.

Microsoft Access Email


#4: Resizable Forms with Anchoring

Easily implement resizable forms with the new anchoring feature. Controls on forms can easily expand to fit the size of the form vertically, horizontally or both, or be anchored to stay in one corner.

Anchoring objects in Microsoft Access forms

Note: It doesn't support adjusting font sizes and proporationally adjusting all controls like the form resizer feature in our Total Access Components product, but its pretty good for no coding.


#5: Search Bar on the Navigation Pane

For all the features we lost with the elimination of the Database Container, the Search Bar on the navigation pane makes up for a lot of it.

With the database container, Access users were trained to look for a particular item in a grid or location by type. Trying to do that in the Navigation Pane is nearly impossible if there are large numbers of objects. This is why existing Access users get so frustrated.

In Access now, a different approach is necessary for finding your object. Rather than visually finding it, enter some letters of the object name in the Search Bar, and the list of objects gets filtered to just those names. This makes it very easy to find objects by name -- especially text within an object name -- that wasn't possible before. Once you are used to this, it's very difficult to go back.

Search Bar on the Microsoft Access 2007 Navigation Pane
Use the Search Bar in Microsoft Access 2007

You need to set the view to All Object Types if you want to see it across object types.

Why Access doesn't automatically default the Navigation Pane to show the Search Bar and all object types is beyond me, but that's easily set and if it's too frustrating, it's good to create a database with all the setting you like then copy that every time you want a new database.

For more information on this feature, visit Take Advantage of the Search Bar in Microsoft Access 2007


#6: Tabbed View to Show Multiple Objects

Showing multiple objects as tabs is a wonderful new feature. Each object is a separate tab so users can easily see the available objects and click on the one they want:

Tabbed view of displaying multiple objects in Microsoft Access 2007

This eliminates the complexity of cascading and overlapping windows. It makes the UI much cleaner and is more intuitive to people used to Internet Explorer and other browsers.

The feature also lets you design your application differently by allowing multiple forms to be opened simultaneously and letting users move between them.

To activate this feature, go under Access Options, Current Database:

Setting the tabbed interface option in Microsoft Access 2007


#7: Datasheet Multi-Select from Drop Down List of Values

As long as filtering is allowed, your datasheets have a dropdown on the column header to let users see a list of values and select one or a subsets to view. There's no longer a need to write a series of OR statements in SQL WHERE clauses--users can just filter interactively.

This is a very nice feature that never existed before. It can even eliminate the need to run a query to show the unique values in a field.

Datasheet Filtering by Drop Down List of Values in Microsoft Access 2007


#8: More Intuitive Filtering Options

Filtering is available by right clicking on a datasheet or form field (or report field in Report View)

Access now provides filtering options that are much clearer and easier for end users to specify their selections.

Gone is the need to enter syntax such as * for partial matches.

The options for date selections is also much easier to select with a variety of common date ranges:

Date Filtering with Built-in Date Specific Ranges in Microsoft Access 2007


#9: Date Picker for Date Fields

About time. Enough said.

Date Picker for Microsoft Access


#10: Enhanced Command Buttons on Forms

Buttons now support graphics, transparency, and hyperlink hand hovering.


#11: Other User Interface Enhancements

Many other enhancement were made for Access databases to look much more modern and easier to use. Here are a few:

  • Datasheet alternating row colors to see rows better
  • In forms, the navigation caption is customizable so you can change "Record" to something more meaningful.

Here's an example from Total Access Emailer for Access 2007, 2010, 2013 and 2016. Notice the transparent Command Buttons with graphics across the top of the form and the datasheet with alternating colors below it:

Enhanced command buttons in Total Access Emailer for sending emails from Microsoft Access 2007


#12: Trusted Locations

You can now specify trusted locations (folders) to avoid the security warnings when opening a database stored in those locations. A trusted location can be for a folder and its subfolders. Any Access database stored there is automatically considered trusted and the user does not have to approve the running of your module code.

This is set under Access Options, Trust Center. Press the Trust Center Settings button and choose Trusted Locations to add your folders.


#13: Multi-value and Attachment Fields

Two new, complex field data types are available in the Access 2007 ACCDB database format:

  1. Multi-value fields
  2. Attachment fields

The ability to specify a subset of values in a multi-value field simplifies database design and storage for many people who have simple one-to-many relationships.

Attachment fields make it easy to embed a file or files in each record. Note that an Access database cannot exceed 2 GB, so this is intended for small files, and not videos.

Attachment field

Tip: Replace the Paperclip Icon of Attachment Fields with a Column Name in Microsoft Access Tables


#14: Image Controls with Control Source

Previously, an image control was used by setting its Picture property by selecting a graphic file from disk. In Access 2007, the image control has a ControlSource property, so it lets you reference a field in the data source similar to a text box. Easily display images from your table.

Image Control with ControlSource property

These are just some of the new Access features I find compelling and nearly impossible to do in Access 2003. Some of these cannot be implemented if you still need to support users with Access 2003, but many can and will have those features if your user runs Access 2016, 2013, 2010 or 2007. Allen Browne has also compiled a detailed list of Access 2007 enhancements. With the free runtime version of Microsoft Access, cost should not be an issue for people needing Access 2007 or later to use your databases.

I realize my enthusiasm for Access 2007 may not be shared by all, so here are a few thoughts if you encounter resistance.

People Dislike Change

Most people dislike change. When it comes to migrating to new technology, people want to be convinced the new features are worth the hassle. This applies to developers and end users.

If people don't adopt new technology, that's fine, but they should understand what they're missing. They may end up spending more time and money trying to implement complex features, when it'd be available in the new platform automatically. Of course, this is not unique to Access.

Be a Technology Thought Leader

This is your opportunity to be a technology thought leader rather than follower. Products go in cycles, and Microsoft Access 2007 was an opportunity to adopt a new approach and leapfrog past versions.

It offers many advances to let you deliver solid productivity gains at less cost to your users. It's critical that advanced Access users and developers familiarize themselves with the new features to educate others on upgrading, and not get passed by when others learn it. To the dismay of many, ribbons were added in Office 2007 and are not going away. That's not a reason to stay.

Leverage Your Existing Investment in Microsoft Access

One of the most important benefits of creating solutions on top of a full featured platform like Access is when the underlying technology is enhanced, a variety of features can be added for little or no cost. We owe it to our users and ourselves to take advantage of these advances. It also gives us as Access developers a competitive advantage over other technologies.

Hopefully you share my enthusiasm for the latest version of Access.


Table Design

Query Design

Form Design

Form Tips and Mistakes

Copy Command Button and Keep Picture

Module VBA to Forms and Controls

Form Navigation Caption

Resync Record in a Subform

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys


Combo Box Top 6 Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppress Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Add Buttons to the Quick Access Toolbar

Collapse the Office Ribbon for more space

VBA Programming

Basics: Forms and Controls

Run VBA Code from a Macro

Use Nz() to Handle Nulls

Avoid Exits in the Body of a Procedure

Shortcut Debugging Keys

Set Module Options

Math Rounding Issues

Rename a File or Folder

Avoid DoEvents in Loops

Age Calculations

Weekday Math

Send Emails with DoCmd.SendObject

Source Code Library

Microsoft Access Modules Library

Microsoft Access Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual SourceSafe

Deployment

Runtime Downloads

Simulate Runtime

Prevent Close Box

Disable Design Changes

Broken References

Remote Desktop Connection Setup

Terminal Services and RemoteApp Deployment

Reboot Remote Desktop

Missing Package & Deployment Wizard

Avoid Program Files Folder

Unavailable Mapped Drives

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Remove 'Save to SharePoint Site' Prompt from an Access Database

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

SQL Server Upsizing

Microsoft Access to SQL Server Upsizing Center

Microsoft Access to SQL Server Upsizing Center

When and How to Upsize Access to SQL Server

SQL Server Express Versions and Downloads

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

SQL Server Azure Usage and DTU Limits

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Connect with Us

 

Free Product Catalog from FMS