Upgrading is always a bit uncomfortable, but adjusting to the changes between Access 2003 and 2007 was a big challenge. Developers and users alike felt like they are working with a completely different product. Now, the good news is that after an initial adjustment, finding things has become almost as intuitive as it was before — it’s just a different structure. Nonetheless, some of the changes take time to get used to. There are still a few oddball options and settings I have to hunt for every time I use them.
Note: This article is also available as a PDF download.
A note about terminology
Since its inception, Access has used the mdb file format. The release of Access 2007 brought a new file format, the accdb file format (based on XML). Access 2007 and 2010 still support mdb databases for backward compatibility. For the purposes of this article, just keep in mind that mdb files represent Access 2003 and earlier; accdb files represent Access 2007 and 2010.
1: The Database window
It’s gone, so don’t go looking for the Database window. You won’t find it. Microsoft replaced the Database window with the Navigation Pane, shown in Figure A. In this case, it’s not so much about finding something — it’s that you don’t like what you find! The Navigation Pane is a huge adjustment for users as well as developers.
The new Navigation Pane displays database objects; it replaces the mdb Database window.
The Navigation Pane is similar to the Database window in that it corrals all the objects in one place. Unlike the Database window, you can see all objects at the same time, depending on the current view. In addition, it’s stuck to the left side of the screen; you can’t move it. You can collapse it by clicking the Open/Close button in the top-right corner.
The biggest adjustment isn’t the pane itself, but the loss of the Database Window toolbar. Those options have moved to the Create tab, shown in Figure B. That’s where you’ll go to insert new objects and launch wizards. This new tab also replaces the New Object button (on the Database toolbar).
Use the Create tab to insert objects.
2: Hiding and displaying objects
Hiding an object isn’t any harder in Access 2007 or 2010, but unhiding objects is a little tricky. To hide an object in Access 2007/2010, do the following:
- Right-click the object in the Navigation Pane.
- Choose Table Object | View Properties. (The qualifiers are different for each object.)
- Check the Hidden option, shown in Figure C.
- Click OK.
Hiding objects is still easy.
Unhiding an object is less intuitive in the accdb versions. You might browse the application options (via the Object button or the File tab — that seems the most likely spot. It’s actually easy to find, but you have to know where to start:
- Right-click the Navigation Pane’s title bar.
- Choose Navigation Options.
- In the Display Options section, check the Show Hidden Objects option, shown in Figure D.
- Click OK.
The Navigation Pane’s shortcut menu doesn’t seem the mostly likely route to unhiding database objects.
In earlier versions, once you opened an object you could click the View button on the object’s toolbar to switch views. Although the button offered several views, the default was object view or Design View — so a single click sufficed to switch back and forth. That View button still exists, but it’s on the Home tab. And the accdb versions don’t automatically switch to the Home tab when you open an object. Instead, the tab that’s active when you open the object remains active. You can click the Home tab, but it just seems unnatural after all those years of having a single-click route to both views.
It’s a small thing, but I still find myself trying to click the View button. If you right-click the object’s title bar, you can choose Design View from the resulting context menu, just as you could before.
4: Table templates
Access 2007 has five table templates you can use to generate new tables with common fields: Comments, Contacts, Issues, Tasks, and Users. With a table open in Design view, click New Field to access these templates. It’s easy to use. But the feature is totally missing in 2010, which offers Application Parts instead. To build a table using this feature, do the following:
- Open a blank database and close Table1.
- Click the Create tab.
- Click Application Parts in the Templates group.
- Select a form template or one of the Quick Start options, shown in Figure E, to generate objects.
Choose an option from the Application Parts gallery.
The AutoFormat feature has certainly changed over the years. In mdb versions, you select an object (in Design view) and choose AutoFormat from the Format menu — easy. In Access 2007, this feature is still easily assessable via the AutoFormat group on the Form Layout and Report Layout tabs.
However, Access 2010 replaces AutoFormat with Themes, which are customizable and can be downloaded or shared with other users via a server. You’ll find the Themes gallery, shown in Figure F, in the Themes group on the Design tab. Themes is an Office feature, not limited to just Access.
Access 2010 Themes replace AutoFormat.
If you really miss AutoFormat, you can add it to a custom Ribbon. Adding it to the Quick Access Toolbar offers no advantage because it’s a context-sensitive feature. AutoFormat on the Quick Access Toolbar is available only when Themes is available.
6: Renaming objects
Renaming an object in the mdb Database window was a simple task. You clicked the object twice — not a quick double-click, but two single clicks. Then, with the name in edit mode, you could simply replace the name right in the Database window.
To rename an object in the Navigation Pane, right-click the object and choose Rename to access edit mode. It’s another simple change that should be easy to adjust to but isn’t. Invariably, I click the object a few times before it hits me… right-click! My guess is Microsoft thought the two single-click access left objects a bit too vulnerable to accidents.
7: The ruler
The mdb form and report objects display a ruler in Design view. It’s a default setting that’s easy to toggle by choosing Ruler from the View menu. To find it in an accdb file requires a few more clicks. With the form or report in Design view, click the Arrange tab. Then, click the Size/Space drop-down in the Sizing & Ordering group and select Ruler. It’s still a toggling option.
8: Saving as mdb
As you might expect, accdb versions have backward compatibility. With no effort on your part, you can open an mdb file in Access 2007 without altering the file’s format. Similarly, you can save an accdb to the older mdb file format. If you want to convert the accdb format to mdb, use the Save & Publish option on the Office button or File tab. This isn’t a frequent task for most users, so it’s easy to forget that you must use the Save & Publish option.
9: User Level Security
For better or worse, accdb versions don’t support the mdb User Level Security model. If you go looking for this feature, you won’t find it.
To maintain backward compatibility, the accdb interface will let you apply and update User Level Security, but the database itself must be in mdb file format. After downgrading to mdb format, the Database Tools tab displays a new group called Administer. From the Users And Permissions drop-down, you can apply or update User Level Security.
User Level Security is available in Access 2010, also only for mdb files. Click the File tab, choose Info, and choose the appropriate option from the Users And Permissions drop-down.
10: Print Preview
Print Preview is on the mdb file’s Database menu — one click is all it takes to view the current object as it will print. The accdb placement makes sense, but it certainly isn’t convenient. Click the File tab, choose Print in the left pane, and click Print Preview. When you’re done, click Close Print Preview. Print Preview was one of the first options I added to the Quick Access Toolbar.
11: Startup options
Startup settings let you personalize the database a bit. You can add a title and icon to the title bar, specify an opening form, hide the Database window, and more. The options are available from the Startup command on the Tools menu.
To access these settings, click the File tab, and choose Options. Click Current Database, and you’ll find the settings in the Application Options section. Renaming the options from Startup to Application Options makes sense, but it does make them harder to find.
Check out 10 Things… the newsletter
Get the key facts on a wide range of technologies, techniques, strategies, and skills with the help of the concise need-to-know lists featured in TechRepublic’s 10 Things newsletter, delivered every Friday. Automatically sign up today.