Data Management

10+ things I can never find in Access 2007/2010

Even after working a while with Access 2007 or 2010, you may be disoriented if you're used to earlier versions. Susan Harkins offers some help in locating the tools you need.

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.

Figure A

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

Figure B

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:

  1. Right-click the object in the Navigation Pane.
  2. Choose Table Object | View Properties. (The qualifiers are different for each object.)
  3. Check the Hidden option, shown in Figure C.
  4. Click OK.

Figure C

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:

  1. Right-click the Navigation Pane's title bar.
  2. Choose Navigation Options.
  3. In the Display Options section, check the Show Hidden Objects option, shown in Figure D.
  4. Click OK.

Figure D

The Navigation Pane's shortcut menu doesn't seem the mostly likely route to unhiding database objects.

3: Views

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:

  1. Open a blank database and close Table1.
  2. Click the Create tab.
  3. Click Application Parts in the Templates group.
  4. Select a form template or one of the Quick Start options, shown in Figure E, to generate objects.

Figure E

Choose an option from the Application Parts gallery.

5: AutoFormat

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.

Figure F

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

18 comments
james_dono
james_dono

When I had long processes using multiple queries, I would use the descriptions as a way of organizing them, kind of like groups. I also used the Description field of tables to tell me the number of records in a table, which would help me sometimes keep them straight. I miss not being able to sort by description, or even to easily see the descriptions. I absolutely HATE the Help system. I'm in a query, and I want the syntax for a specific function. I don't want to search the entire Office online database! I want to search the Access funtions. I can't even find them, much less limit the search to them. If I were Microsoft, I would be extremely embarrassed that my users have to use Google to be able to get help with Access. Right now, I'm trying to find out why the Properties tab in a linked table does not list the location of that table. Or maybe the little icon to the left of the name isn't a linked table? Then what is it?

jimbritttn
jimbritttn

The "Ribbon" is also one of the most intrusive, screen wasting, confusing, thing Microsoft has ever done! Come on Microsoft, GIVE US A CHOICE of this monster or the more efficient and logical menu bar!

jimbritttn
jimbritttn

Changing the main interface to that stupid bar on the left is horrible! I used to use the "Comments" field as a 2nd sort field on complicated database but with this really dumb setup that field is totally useless! HOPEFULLY some 3rd party programmer with COMMON SENSE (lacking at Microsoft!) will come up with an add-on that will give us the logical interface of Access XP back!

ArkyBass
ArkyBass

I found the updated rename method a pain also. The shortcut key F2 allows the rename when you have focus on the object. I also use this for copy/past the object name in vba or into documentation. Another warning is that if you don't use the new features of 2010 you can open/edit in 2007. However once you compact/repair it will not open with 2007.

bdsfgsfdgsdfgsdfgsdfgsdfgsdfgsdfg
bdsfgsfdgsdfgsdfgsdfgsdfgsdfgsdfg

Thanks for the article, I have used MS Access for 15 years and I hate the recent version interface. I used the properties window a lot for my tables and queries. To indentify them, sort them, and group work flows. Identify what was a test and what was part of the process. Now I can not find the descriptions and they do not appear in the navigation "pain". I have enough work to do from my Boss than have Bill Gates make me learn things all over again.....

hammberry
hammberry

Why not a PDF? I don't want to be annoying, but if you're not going to offer a PDF, why suggest it's available?

hammberry
hammberry

I've been trying to download the PDF, without success. I've reported this as a missing link. Is this a lost cause?

tmillar
tmillar

Not some thing I can't find, but I hate the way Access joins the labels and text boxes when you click the "Report" button in the "Create" group. If you right-click a text box, then choose "Remove" from the "Layout" submenu, you can undo the join, but it still messes things up. Needless to say, I never create reports this way. Does anyone find this useful?

doublevdesign
doublevdesign

Re 3: Views. There are small icons for the different views at the lower right of the window which allow you to change with one click.

Dave O
Dave O

I sure wish MS Office Labs had written their Search Commands utility to work in Access. It only works for Word, PowerPoint, and Excel. It would save me the ton of time I spend searching the help file or articles such as this one.

fennemore
fennemore

Thank you Susan, as always pearls before me. W7 and Access 2010 feeds the needs of the "Cutting and Sticking" brigade not those working for a living ! Most I have spoken to have felt like throwing their computer through the window.

ssharkins
ssharkins

They're there, but the context-sensitive behavior has changed and I'm certainly having a hard time adjusting. Another one that continues to throw me is the loss of the "document" Close button in Word. If you only have one document open and you click Close in the title bar, it closes Word -- this one change is driving me nuts!

staffordd
staffordd

I am not so sure that it will get to the point where the environment no longer supports my old, old versions of Access. If you think about it, I am running Access 2000 on an operating system that came along nearly 10 years later. And it runs PERFECTLY, no errors, no problems. In Windows 7 32 bit. With Windows 7 being a new operating system, I assume it will be around for a couple years at least. If they change to...Windows 9, and my Access 2000 doesn't run under it - you know what I will do. Keep a Windows 7 machine going till the end of time. I've got installation discs, so I should be able to keep it going for a LONG time. So I am not sure I always buy into "newer is better" and "more features, MORE, MORE features" and "it's faster" and "it's modern, it's really cool" - it sounds like the few users who have responded, are STRUGGLING. I am not having ANY problems whatsoever with my Access 2000, or my Access 2000 or 97 at work - which means that while I am ten years "behind the times", my employers have me beat, running a THIRTEEN year old app. Granted, we are still on XP at work, but hey - Access 97 is STILL 13 no matter what the operating system. I would bet good money that Access 97 would run FINE under Windows 7 (given the way Windows 7 has dealt with my odd assortment of vintage apps - perfectly - I have a lot of confidence in it already). So I'm not really afraid that my 2000 will stop running, because I'll keep a "just old enough" OS running for as long as I possibly can. I'll check back in with Tech Republic in the year 2020, and I will tell you if I am still able to run Access 2000 at home. Any bets? I bet it will still be running away happily, probably under Windows 17 by that time :-) Gods, what a thought. SHUDDER. What will Windows look like in 20 years? peace, love and happiness dave :-)

staffordd
staffordd

This is a very interesting article, Susan always always writes about interesting things. I'm a long time Access user, I started with 95, then 97, then 2000 - and then I stopped. I've seen - 2003? I've never looked at 2007 or 2010. At work, I have the option of using 97 or 2000, which is fantastic, because those are the two versions that I have the MOST skill in, and the most experience as well in 97. At home, I use 2000, and again, that's just where I stopped. Now, normally, I would be all for upgrading to a newer version. I mean, at home, I am running Win 7 64 bit - so - why, I ask myself, am I still - STILL running Access 2000? Because of articles like this one. Because every time I see or hear ANYTHING about ANY version of Access post 2000....it's usually quite, or very, negative. So - I would turn this question around to you Susan, and also to any readers/commenters. If you are a user of Access 2010, or Access 2007 - tell me REALLY, and I mean really, what are the REAL advantages compared to what an experienced user can do with Access 2000 ? What do I "get" out of switching to Access 2010 (besides a headache, frustration, and ... THEMES....) shades of Windows shudder across my mental screen...THEMES ! I would be curious. I have no access to any of these newer versions. My gut feeling, based on what I've read, is that I would "get" more frustration out of them than I would "get" real, added functionality or convenience. But convince me - explain why I WANT Access 2007 or 2010. Unless you can't???? Please advise. I am very interested to hear your thoughts. Thanks everyone. Old-Fashioned Dave (Access 97/2000 user) Ten Years out of date, and quite happy.... cheers! d

ssharkins
ssharkins

The ribbon interface is still rather intuitive, it's just so different that it's going to take time to adjust. Once you do so, you will find it much easier to find commands -- just like with the menu, you will know where to go (mostly).

ssharkins
ssharkins

The number one reason to upgrade is that MS will eventually stop supporting the older versions. Now, that won't matter to some, and if not, stay with what works for you. The truth is, I like Access 2010 -- I didn't like Office 2007 and so it seems a bit odd to know say I like Office 2010, but it seems to me like many of the 2007 irritants have been fixed. In addition, now that I have my head wrapped around the ribbon interface, I can find most things -- it's just like the menu feature, once you understand the hierarchy, it makes sense and you know where to go looking for things (mostly). Can't speak for development though -- developers hate the new accdb format. Most are

ssharkins
ssharkins

If you get your work done with an older OS and apps, you won't hear me nag you to upgrade. The only problem is that eventually, you will fall from the radar -- but if that doesn't happen before you retire, does it even matter???? :) YMMV (your mileage may vary)-- my motto. :)

james_dono
james_dono

I can't think of a single reason to switch to a later version. The only reason I am doing so is because my clients buy new machines, and they have to buy a new version of Office, and I have to support their version. If it was me, I'd still be working with Access97, as well.

Editor's Picks