Access 2010: The Missing Manual

The Modern Face of Access

Ever since Microsoft Office conquered the world (way back in the 1990s), programs like Word, Excel, and Access haven’t changed a lot. Although a genuinely useful new feature appears once in a while, Microsoft spends more time wedging in odd gimmicks like a talking paper clip. But after spending far too long trying to simplify the haphazard, toolbar-choked interfaces in most Office applications, Microsoft finally worked up the courage to redesign it all from scratch, throwing out the menus and toolbars and replacing them with a slick new ribbon.

Access 2010 inherits the revamped user interface from Access 2007 and adds a few refinements of its own. Chief among them is something called backstage view, a central hub for creating, opening, and saving database files.

Word To The Wise: When Access Isn’t Enough

If you’ve picked up this book, you probably have a good sense that Access will meet your needs. But if you’re in any doubt, a quick reality check will confirm whether you’re on the right path.

The following list describes a few warning signs that suggest you and Access just aren’t a good fit. If you don’t fall into any of these categories, congratulations—you’re ready to use the most straightforward and productive database software anywhere!

• You need to store huge volumes of information (more than 2 gigabytes of data). You’re unlikely to hit this mark unless you’re storing large pictures or other types of digital content inside a database. Even a big Access database is usually less than 100 megabytes (about 20 times smaller than the 2 GB limit).

• You’re going to share your database on a network, and more than a dozen people need to use it at once. It’s difficult to correctly interpret this limit. It’s perfectly fine for hundreds of people to use your database from time to time, but problems occur when many people are all jockeying to make changes to the same database file at the same instant. You need to test your database to figure out whether you can cross this limit without introducing problems, and you may need to use Access in conjunction with SQL Server or Share-Point. Chapter 19 has more information about sharing Access with groups of people.

• You need to use your database to power a web application. Ordinary Access databases just aren’t cut out for the Web. But Access 2010 introduces a new web database feature that uses Microsoft SharePoint behind the scenes, so your database can serve far more people than it could through Access alone. However, there are disadvantages—for example, some Access features don’t carry over to the web application, and you must invest in SharePoint server or a SharePoint hosting service. Chapter 23 describes Access’s web database feature in detail.

Backstage View

Your data is the star of the show. That’s why Access’s creators refer to databases as being on stage. Sure, it’s a strange metaphor, but the rationale for Access’s backstage view makes sense: It temporarily takes you away from your database and lets you concentrate on other tasks that don’t involve entering or editing data. These tasks include creating a new database, converting your database to a different format, printing part of its contents, and changing Access settings.

Note

Backstage view is a new innovation that appears in the most popular Office programs, including Access, Excel, Word, and PowerPoint.

To switch to backstage view, click the File button that appears just to the left of the Home tab in the ribbon, as shown in Figure 2. To get out of backstage view, just click File again or press Esc.

Figure 2. Click the File button to go backstage. The name of the button is a nod to Access 2003 and other older, more traditional Windows programs, which group many of these tasks together in a File menu.

Backstage view is split into two parts. On the left is a narrow strip listing commands. Click one of these to reveal a screen where you can perform a different task. Depending on what you click, Access may show additional options and information on the right.

When you first start Access, you begin in backstage view (Figure 3). That’s because Access knows that you need to create a new database or open an existing one before you can get to work.

Along with creating and opening databases, you can also use Access’s backstage view to:

• Save a copy of your database (choose Save Database As).

• Compact, repair, and encrypt your database file (choose Info).

• Print some of the information in your database (choose Print).

• Publish your database to a SharePoint server so other people can use it over the Web (choose Save & Publish).

• Configure all sorts of Access options (choose Options).

• Quit Access (choose Exit).

Figure 3. Access starts in backstage view, with New selected (on the left). From this point, you just need to fill in a file name and click the big Create button on the right to get started. Or, click Open to look for an existing database.

You’ll return to backstage view to perform all of these tasks in the chapters ahead.

The Ribbon

The ribbon is a super-toolbar that replaces the various toolbars that clogged the window before Access 2007. It’s clear, streamlined, and carefully organized into tabs—Home, Create, External Data, and so on. Initially, Access starts out with four tabs (although other tabs appear when you perform specific tasks).

When you create or open a new database, you start at the Home tab. Click the Create tab (as shown in Figure 4), and you get access to a slew of powerful commands that let you add new database components.

Here’s a quick snapshot of the four basic ribbon tabs:

• Home gathers together a variety of common commands including the familiar copy-and-paste tools and formatting commands for tweaking fonts and colors. You’ll also find handy features like sorting, searching, and filtering, all of which you’ll tackle in Chapter 3.

  

  Figure 4. The ribbon is full of craftsperson-like detail. When you hover over a button, you don’t see a paltry two- or three-word description in a yellow box. Instead, you see a friendly pop-up box with a complete mini-description. Here, the mouse is hovering over the Table command.

• Create has commands for inserting all the different database objects you’ll learn about in this book (see Understanding Access Databases for the lowdown). These include the tables that store data, the queries that search it, the forms that help you edit it, and the reports that help you print it.

• External Data has commands for importing data into Access and exporting it to other programs. You’ll also find features for integrating with Microsoft SharePoint Server. You’ll use these commands in Part Six.

• Database Tools features the pro tools you’ll use to analyze a database, link tables, and scale up to SQL Server. You’ll also find the commands for inserting Visual Basic code, which you’ll explore in detail in Part Five.

It’s worth spending some time getting accustomed to the tab-based ribbon. Try clicking one tab after the other, rifling back and forth through the four sections to see what they hold. You’ll learn more about all these commands as you make your way through this book.

Tip

If you have a scroll mouse, you can breeze through the tabs even faster by moving the mouse pointer over the ribbon, and then rolling the scroll wheel up or down.

One nice ribbon feature is the way it adapts to different window sizes. In a wide Access window, there’s room to spread out, and text appears next to almost every button. But in a narrow Access window, where space is more limited, Access strips the text off less important buttons to make room (Figure 5).

When space is limited, Access may also replace large images with smaller images and rearrange the buttons in a particular section of the ribbon, although these tactics are much less common. Most of the time, Access tries to keep its ribbon tabs ruthlessly consistent, so you won’t get thrown off by different button arrangements when you resize the Access window.

Figure 5. Here are three sections from the Home tab in the ribbon (Sort & Filter, Records, and Find). When the Access window is wide, there’s plenty of room to show buttons and text (top). But if you resize the Access window down to super-skinniness, the ribbon removes text so it can keep showing the same set of commands. If you want to know what a no-text button does, hover your cursor over it to see its name.

Tip

Want to reclaim the screen real estate that the ribbon occupies? Just double-click the current tab, and the ribbon collapses, leaving only the row of tab titles visible. Double-click the tab again to pop the buttons back into sight.

Using the Ribbon with the Keyboard

If you’re a diehard keyboard lover, you’ll be happy to hear that you can trigger ribbon commands with the keyboard. The trick is to use keyboard accelerators, a series of keystrokes that starts with the Alt key (the same keys you used to use to get to a menu). When using a keyboard accelerator, you don’t hold down all the keys at the same time. (As you’ll soon see, some of them have enough letters to tie your fingers up better than the rowdiest game of Twister.) Instead, you press the keys one after the other.

The trick to keyboard accelerators is to understand that once you press the Alt key, you do two things, in this order:

1. Pick the correct ribbon tab.

2. In that tab, choose a command.

Before you can trigger a specific command, you must select the right tab (even if you’re already there). Every accelerator requires at least two key presses after you press the Alt key. You’ll need even more if you need to dig through a submenu.

By now, this whole process probably seems hopelessly impractical. Are you really expected to memorize dozens of different accelerator key combinations?

Fortunately, Access is ready to help you out with a new feature called KeyTips. Here’s how it works: Once you press the Alt key, letters magically appear over every tab in the ribbon. Once you press a key to pick a tab, letters appear over every button in that tab. You can then press the corresponding key to trigger the command. Figure 6 shows how it works.

Figure 6. Top: When you press Alt, Access pins Key-Tips next to every tab, over the File menu, and over the buttons in the Quick Access toolbar. Bottom: If you follow up by pressing Y (for the Database Tools tab), you’ll see letters next to every command in that tab. Now you can press another key to run a command (for example, W moves your data to SQL Server).

Tip

Don’t bother trying to match letters with tab or button names—the ribbon’s got so many features packed into it that in many cases, the letters don’t mean anything at all.

Nostalgia Corner: Access 2003 Menu Shortcuts

If you’ve worked with a previous version of Access, you may have trained yourself to use menu shortcuts—key combinations that open a menu and pick out the command you want. When you press Alt+E in Access 2003, the Edit menu pops open (in the main menu). You can then press the S key to choose the Paste Special command.

At first glance, it doesn’t look like these keyboard shortcuts amount to much in Access 2010. After all, Access 2010 doesn’t even have a main menu! Fortunately, Microsoft went to a little extra trouble to make life easier for longtime Access aficionados. You can still use your menu shortcuts, but they work in a slightly different way.

If you press Alt+E in Access 2010, a tooltip appears over the top of the ribbon (Figure 7) that lets you know you’ve started to enter an Access 2003 menu shortcut. If you go on to press S, then you wind up at the familiar Paste Special dialog box, because Access knows what you’re trying to do. It’s almost as though Access has an invisible menu at work behind the scenes.

Of course, this feature can’t help you out all the time. It doesn’t work if you’re trying to use one of the few commands that don’t exist any longer. And if you need to see the menu to remember what key to press next, you’re out of luck. Access just gives you the tooltip.

Note

In some cases, a command may have two letters, and you need to press both keys, one after the other. You can back out of KeyTips mode at any time without triggering a command by pressing the Alt key again.

Some other shortcut keys don’t use the ribbon. These key combinations start with the Ctrl key. For instance, Ctrl+C copies highlighted text, and Ctrl+S saves your current work. Usually, you find out about a shortcut key by hovering over a command with the mouse cursor. Hover over the Paste button in the ribbon’s Home tab, and you see a tooltip that tells you its timesaving shortcut key is Ctrl+V. And if you’ve worked with a previous version of Access, you’ll find that Access 2010 keeps most of the same shortcut keys.

Figure 7. By pressing Alt+E, you’ve triggered the “imaginary” Edit menu. You can’t actually see it (because it doesn’t exist in Access 2010). However, the tooltip lets you know that Access is paying attention. You can now complete your action by pressing the next key for the menu command.

The Quick Access Toolbar

Keen eyes will notice the tiny bit of screen real estate that sits on the Office button’s right side, just above the ribbon (Figure 8). This bit of screen holds a series of tiny icons, and it’s called the Quick Access toolbar (or QAT to Access nerds).

If the Quick Access toolbar were nothing but a specialized shortcut for three commands, it wouldn’t be worth the bother. However, the nifty thing about the Quick Access toolbar is that you can customize it. In other words, you can remove commands you don’t use and can add your own favorites.

Microsoft has deliberately kept the Quick Access toolbar very small. It’s designed to give a carefully controlled outlet for those customization urges. Even if you go wild stocking the Quick Access toolbar with your own commands, the rest of the ribbon remains unchanged. (And that means a coworker or spouse can still use your computer without suffering a migraine.) However, Access 2010 also lets you get more radical by revising the arrangement of tabs, sections, and buttons in the ribbon. To learn how to customize the QAT and the ribbon, check out the appendix.

Figure 8. The Quick Access toolbar puts the Save, Undo, and Redo commands right at your fingertips.Access singles out these commands because people use them more frequently than any other commands. But as you’ll learn in the appendix of this book, you can add anything you want here.