Top Features of Microsoft Access 2007 That Aren't Available in Prior
Versions
by Luke Chung,
President of FMS, Inc.
 Why
Upgrade to Access 2007?
There are many experienced Microsoft Access developers
resisting the migration to Microsoft Access 2007. While there are
some changes that are cumbersome, there are many new features that
justify the migration. Access 2007 lets you create applications with
features that in many cases would be impossible to provide in prior
versions.
As power users and 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. Once we understand that, we can justify
migrating to Access 2007. After all, people don't want change just for
technology's sake. They want some real productivity gains.
In this paper, I outline some of the top features I
enjoy from Access 2007 that couldn't be easily done before. But before
we start, here are some reasons why the transition feels painful.
Problems with Microsoft Access 2007
I've been surprised to run into so many users either in
person or in newsgroups saying how much they dislike Access 2007. Some
of the reasons are legitimate:
-
Complaints about the Access/Office 2007 ribbon and
how difficult it is to find things is universal. I must say I was
confused when I first tried to find features I relied upon in those
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 Alt+D as a way 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 command to select the list
of opened objects. Yup, that's gone too.
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.
Overcoming Resistance to Change
For years, the Access community complained that Microsoft was
abandoning Access, that they didn't invest enough in it, that the Jet Engine and
DAO was dead, etc. Well, the Microsoft Access development team for Access 2007
was the largest ever and Microsoft made a huge investment that added
considerable new features to Access.
Then the same people complained that too much changed; that it
was too hard to learn the new features, and generally came up with reasons to
resist changing their ways.
Circular Arguments Against Progress
Well, we can't have it both ways. Either we want the platform to
evolve or we want it to never change and die. I vote for the former because the
payoff is huge and allows us to create solutions we couldn't have before or do
so with less time and cost.
Please don't fall into the trap of resisting change from your
comfort zone. That's a death knell for yourself and the people you serve.
Philosophical: Benefits of Creating Applications with Microsoft Access
Why do we bother to create applications on Microsoft Access
instead of another platform like .NET, Java, VB6?
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.
Cool New Features of Access 2007 I Couldn't Do Before
Here are some things that I've found valuable which are very easy to do in
Access 2007 that were nearly impossible to do in earlier versions:
 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.
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:
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.
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.
It doesn't support
adjusting font sizes like the form resizer feature in our
Total Access Components product, but its pretty
good for no coding.
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 2007, 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.
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
Output to PDF
Once you install the free
Office/Access 2007 add-in for Save as PDF and XPS, this feature is
available. 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, takes advantage of
this feature to let you email filtered Microsoft
Access reports in PDF format to each recipient.
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:
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:
Datasheet Filtering by 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.
Improved Filtering Options
Filtering is available by right clicking on a datasheet or form field
(or report field in Report View)
Access 2007 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:
|
|
Data Picker for Date Fields
About time. Enough said. |
 |
Enhanced Command Buttons on Forms
Buttons now support graphics, transparency, and hyperlink hand hovering.
Other User Interface Enhancements
Many other enhancement were made for Access 2007 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. Notice the transparent Command
Buttons with graphics across the top of the form and the datasheet with
alternating colors below it:
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.
Multi-value and Attachment Fields
Two new, complex field types are available in the Access 2007 ACCDB database
format: Multi-value and 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.
Summary for Upgrading to Access 2007
These are just some of the Access 2007 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 2007. With the free runtime version of Access
2007, cost should not be an issue for people needing Access 2007 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 is one of those
opportunities 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.
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 Access 2007. Whether you do or don't, feel free to comment about it on my blog.
Additional Resources
Hope this helps. Good luck!
|
