Top 10 Productivity Tips for Microsoft Access 2010


Are you just getting used to working in Microsoft Access 2010? Danny Lesandrini shares some tips on how to work around the annoyance factor of the new format and increase your productivity.

I’ve been working with Access 2010 now for about 6 months and I’m finally
getting more productive. I wanted to come up with 10 "Productivivity
Tips" to speed you on your learning curve, but as I documented the things
that I’ve learned, they came out more like "annoyances". Either way,
I hope you find them helpful.

1) Hide/Show the Ribbon

Unless you’ve adopted Access 2007, you’ll find the Ribbon to be a bit
irritating. Menu options aren’t where you left them in Access 2003. Moreover,
the thing takes up so much space it infringes on your work area. You can
"minimize" the ribbon quite simply by double-clicking on one of the
tabs and Access remembers your selection for future sessions.

2) Get used to Right-Clicking

Maybe it’s just me but the simplest things have become hard. The old menu
options for going "into design mode" still exist, but if you’ve
minimized the ribbon (see tip # 1), then it’s going to take an extra click …
or two to get there. In Access 2003, one click could toggle you between Form
View and Design View. Now, after showing the ribbon (first click), the default
view is "Layout View" so you must click (second click) on the drop
down arrow to see the Design View option (third click).

I suppose I’m just lazy but I’ve taken to using the Right-Click menu. You
can right-click a form, report or query to expose the View menu. That makes it
a consistent 2-click process. However, there’s another discrete little toolbar
in the lower right corner that exposes the available View Menu options. This is
a "one-click" solution I can live with, once I get used to looking
there for it.

Microsoft Access 2010 View Menu options

As for opening things in the Access Navigation bar at the left, the only two
ways I know of is to double click the object (which opens it) or to right-click
and choose the type of view you wish. Like I said, get used to right-clicking.

3) Finding a Code Window

Maybe this is just my frustration with the menu showing again but I used to
open the code window for a form or report with the menu-toolbar option. It’s
more complicated now because if, while working on a form in design view, you
end up with the ribbon on the Create tab, the option labeled "Module"
does NOT open the module behind your form but, as the name suggests, creates a
NEW module.

While this is logical, it requires a diligence that I’m not accustomed to.
Not only do I have to look for menu options but now I have to pay attention to
which menu tab is active.

To bypass this potential hazard, I now simply press Ctl+G to open the
Immediate Window thereby making the VBA editing environment available. I still
have to locate my form in the object browser to load that particular code
module, but at least I’ve stopped creating blank modules which I must later
delete.

4) Home, Create, Design

While we’re on the subject, the three tabs that are most important to get to
know are the Home, Create and Design tabs and of these, Home is the least
useful. The Create tab contains all the options for making new tables, queries,
forms, etc. The Design tab allows you to work with these newly created objects,
turning a SELECT query into an UPDATE query and dropping controls on your form.
Once you’ve got controls, the Home tab is where you go to apply text
formatting. It’s also where the usual run-time options live, such as sorting a
datasheet or filtering a form.

5) Use the Quick Access Toolbar

Things like printing are also, in my opinion, too hard. What used to be one
click is now three. That is, unless you add the print or print preview options to
the Quick Access Toolbar. As a rule, until one gets used to the menu options,
it’s a good idea to overload this toolbar with things you can’t easily find in
the menus.

6) Try out Navigation Forms

Navigation forms provide an alternative to the Switchboard of previous
Access versions. The menu option on the Create tab (shown below) will guide you
through the process and it’s very intuitive. In the beta, the display and
positioning of buttons was glitchy but I haven’t noticed a problem in
production code.

Microsoft Access 2010 Navigation forms

7) Output Reports to PDF

For some time I’ve been using Cute-PDF to output my invoices and other
reports to PDF. PDF output support is now incorporated into Access 2010 though
I believe it is more easily implemented through VBA code. From the menu system,
it takes about 5 clicks to get it done as can be seen in the screen shot below.
This is one of those good candidates for the Quick Access toolbar, which turns
it into a single click.

=> File Menu => Save and Publish => Save Object As => PDF or XPS => Save As

Output Reports to PDF

8) Just Enable All Macros

If you, like me, hate that security warning message, then just enable all
macros. I know it says it’s not recommended, but why? Because if you’re not
careful, code might execute in this file? I hope so … that’s why I wrote it.
To disable the warning you need to enable macros. Find the Access Options from
the File menu and navigate to the Trust Center options. Select the Enable All
Macros option and save.

Enable All Macros

9) Take Advantage of Query Intellisense

Yes, Intellisense is now available while creating queries. It shows up in
the Criteria row and the Update To row for an UPDATE query. Auto-complete will
assist you in getting table and column names correct. For example, if you start
typing your table.column name, a list pops up from which you can select.
Functions are also exposed in the Intellisense auto-complete list.

For years, I’ve been advocating using SET and GET functions to save and
recall values for filtering data. For example, a report might be launched from
a form that collects an ID or text code. You store this value using a SET
function. Then the query behind the report uses a GET function call to recall
the value. In Access 2010, your functions are exposed with Intellisense from
the criteria row, as shown in the screen shot below.

Query Intellisense

The same is true for built-in functions so regardless of whether you use my
SET-GET paradigm or not, this feature may come in handy. On the other hand, you
now have to be careful when typing so that Intellisense doesn’t misunderstand
and auto-complete something you didn’t intend.

10) Look to the Experts for More Tips

As I work with Access 2010, I’m constantly on the lookout for things that
will save me time. I recently learned that some old friends from the Access
newsgroups, Arvin Meyer and Douglas J. Steele, have written a book titled Access Solutions: Tips, Tricks, and Secrets from Microsoft Access
MVPs
. The book is published by Wiley and these authors are two of the most
respected MVPs in the Access community. From the published table of contents
it’s clear they cover topics that apply to previous versions of Access as well
as Access 2010 specific features. If you’re hungry for more tips, it’s the
place to look.

Conclusion

From what I’ve written you may conclude I’m a irritated with Access 2010. I
can’t deny I’ve experienced a little frustration but I stick by what I said
about Office 2010 a year ago when it first came out in beta. Access 2010 is the
best new release of Access in years, maybe ever. Like me, you’ll have to give
up your Access 2003 ways and embrace the new menu system but I think you’ll
find it worth the effort.

»


See All Articles by Columnist

Danny Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles