Access 2007 for Starters: The Missing Manual

Saving and Opening Access Databases

Unlike other programs, Access doesn't require that you save your work. It automatically saves any changes you make.

When you create a new database (Section 1.2.1), Access saves your database file. When you add a table or another object to the database, Access saves the database again. And when you enter new data or edit existing data, Access saves the database almost instantaneously.

GEM IN THE ROUGH: Shrinking a Database

When you add information to a database, Access doesn't always pack the data as compactly as possible. Instead, Access is more concerned with getting information in and out of the database as quickly as it can.

After you've been working with a database for a while, you might notice that its size bloats up like a week-old fish in the sun. If you want to trim your database back to size, you can use a feature called compacting. To do so, just choose Office button → Manage → Compact and Repair Database. The amount of space you reclaim varies widely, but it's not uncommon to have a 10 MB database shrink down to a quarter of its size.

The only problem with the database compacting feature is that you need to remember to use it. If you want to keep your databases as small as possible at all times, you can switch on a setting that tells Access to compact the current database every time you close it. Here's how:

1. Open the database that you want to automatically compact.

2. Choose Office button → Access Options. Access opens the Access Options window where you can make a number of configuration changes.

3. In the list on the left, choose Current Database.

4. In the page on the right, turn on the Compact on Close" checkbox.

5. Click OK to save your changes.

You can set the "Compact on Close" setting on as few or as many databases as you want. Just remember, it's not switched on when you first create a new database.

This automatic save process takes place behind the scenes, and you probably won't notice anything. But don't be alarmed when you exit Access and it doesn't prompt you to save changes, as all changes are saved the moment you make them.

Making Backups

The automatic save feature can pose a problem if you make a change mistakenly. If you're fast enough, you can use the Undo feature to reverse your last change (Figure 1-12). However, the Undo feature reverses only your most recent edit, so it's no help if you edit a series of records and then discover the problem. It also doesn't help if you close your table and then reopen it.

Figure 1-12. The Undo command appears in the Quick Access toolbar at the top left of the Access window (circled), so it's always available.

For these reasons, it's a good idea to make frequent database backups. To make a backup, you simply need to copy your database file to another folder, or make a copy with another name (like Bobblehead_Backup1.accdb). You can perform these tasks with Windows Explorer, but Access gives you an even easier option. Just choose Office button → Manage → Back Up Database, and Access creates a copy of your database for you, in the location you choose (Figure 1-13).

Figure 1-13. When you choose Office button → Manage → Back Up Database, Access fills in a suggested file name that incorporates the current date. That way, if you have several backup files, you can pick out the one you want.

Note

It's still up to you to remember to back up your database. Access doesn't include an automatic backup feature, but you can use another tool to periodically copy your database file. One example is the Windows Task Scheduler that's included with most versions of Windows. (You can read a quick no-nonsense Task Scheduler tutorial at www.pctechguide.com/tutorials/ScheduleTasks.htm.)

Saving a Database with a Different Name or Format

If you want to save your database with a different name, in a different place, or using an older Access file format, you can use the trusty Save As command. Choose Office button → Save As, and then use one of the options in Figure 1-14. Note that, once Access creates the new database file, that file's the one it keeps using. In other words, when you create a table or edit some data, Access updates the new file. (If you want to go back to the old file, you either need to open it in Access, or use Save As again.)

Figure 1-14. Make sure you click the right-pointing arrow next to the Save As menu command to see this submenu of choices. (Just clicking Save As performs the default option, which saves a copy of the currently selected database object, not your entire database.) Then, choose one of the options under the "Save the database in another format" heading.

Opening a Database

Once you've created a database, it's easy to open it later. You can use any of these approaches:

• Double-click a database file. (You can browse to it using My Computer, Windows Explorer, or just plop in on your desktop.) Remember, Access databases have the file extension .accdb or .mdb.

• Launch Access, and then look for your database in the Open Recent Database section on the right of the Getting Started page. (The same list's available through the Office menu, as shown in Figure 1-15.)

• Launch Access, choose Office button → Open, and then browse for your Access database file.

Figure 1-15. The Office menu's Recent Documents list has the same list of files as the Open Recent Database section on the Getting Started page. But if you already have a database open, the Recent Documents list's more convenient, because you don't need to head back to the Getting Started page.

When you open a database, you'll notice something a little bizarre. Access pops up a message bar with a scary-sounding security warning (Figure 1-16).

Figure 1-16. This security warning tells you that Access doesn't trust your database—in other words, it's opened your file in a special safe mode that prevents your database from performing any risky operations.

The security warning's a bit confusing, because right now your database doesn't even attempt do anything risky. However, when you start using action queries (Chapter 7), it's a different story. At that point, you may want to reconfigure Access so it recognizes your files and learns to be a bit more trusting.

In the meantime, you're probably wondering what you should do about the message bar. Just click the X at the right side of the message bar to banish it. (It'll reappear the next time you open the database.)

Opening More Than One Database at Once

Every time you use the Office button → Open command, Access closes the current database, and then opens the one you chose. If you want to see more than one database at a time, you need to fire up more than one copy of Access at the same time. (Computer geeks refer to this action as starting more than one instance of a program.)

It's almost embarrassingly easy. If you double-click another database file while Access is already open, then a second Access window appears in the taskbar for that database. You can also launch a second (or third, or fourth…) instance of Access from the Start menu, and then use Office button → Open to load up a different database in each one.

FREQUENTLY ASKED QUESTION: What's with the .laccdb File?

I see an extra file with the extension .laccdb. What gives?

So far, you've familiarized yourself with the .accdb file type. But if you're in the habit of browsing around with Windows Explorer, you may notice another file that you didn't create, with the cryptic extension .laccdb. Along with Bobblehead.accdb, you may spot the mysterious Bobblehead.laccdb.

Access creates a .laccdb file when you open a database file and removes it when you close the database, so you'll see it only while you (or someone else) is browsing the database.

Access uses the laccddb to track who's currently using the database. The / stands for lock, and it's used to make sure that if more than one person's using the database at once, people can't make changes to the same record at the same time (which could cause all manner of headaches).

Access 2007: The Missing Manual covers more on how Access works with multiple users. All you need to know is that it's safe to ignore the .laccddb file. You don't need to include it in your backups.

Opening a Database Created in an Older Version of Access

You can use the Office button → Open command to open an Access database that somebody created with a previous version of Access. (See the box "Sharing Databases with Older Versions of Access" in Section 1.2.3 for more about different Access file formats.)

Access handles old database files differently, depending on just how old they are. Here's how it works:

• If you open an Access 2002-2003 file, you don't get any notification or warning. Access keeps the current format, and you're free to make any changes you want.

• If you open an Access 2000 file, you're also in for smooth sailing. However, if you change the design of the database, the new parts you add may not be accessible in Access 2000.

• If you open an older Access file (like one created for Access 97, 95, or 2.0), Access asks whether you want to convert the database or just open it (see Figure 1-17).

Figure 1-17. Access gives you a choice when you open a database file that was created in Access 97, 95, or 2.0. If you choose to convert the database (click Yes), Access copies the existing database into a new database file, in Access 2002-2003 format. You can then edit this copy normally. If you choose to open the database (click No), Access opens the original file without making a copy. You can still edit existing data and add new data, but you can't change the database's design.

POWER USERS' CLINIC: Changing the Folder Access Uses for Databases

Access always assumes you want to store databases in the My Documents folder. And though you can choose a different location every time you save or open a database, if there's another folder you need to visit frequently, then it makes sense to make that your standard database storage location. You can configure Access to use this folder with just a few steps:

1. Choose Office button → Access Options. The Access Options window appears.

2. In the list on the left, choose Popular.

3. In the page on the right, look for the "Creating databases" heading. Underneath, you'll find a "Default database folder" text box. Type in the folder you want to use (like C:\MyDatabases), or click Browse to navigate to it.

When you're finished, click OK to save your changes.

Tip

You can always tell the current database's format by looking at the text in brackets in the Access window's title bar. If you open an Access 2002-2003 file, the title bar might read "Bobblehead: Database (Access 2002-2003 file format)".

When you open an old-school Access database, you'll notice something else has changed. When you open a table, it won't appear in a tabbed window (like the ones shown in Figure 1-20). Instead, the table opens in an ordinary window that can float wherever it wants inside the main Access window. This seems fine at first, until you open several tables at once. Then, you're stuck with some real clutter, as shown in Figure 1-18.

Figure 1-18. In an old-style Access database, different windows can overlap each other. It's not long before the table you want is buried at the bottom of a stack of windows.

This somewhat unfriendly behavior is designed to be more like previous versions of Access. But don't worry—you can get back to the slick tabs even if you don't convert your database to the new format. All you need to do is set a single configuration option for your database:

1. Choose Office button → Access Options. The Access Options window appears.

2. In the list on the left, choose Current Database.

3. Under the Application Options heading, look for the Document Windows Options setting, where you can choose Overlapping Windows (the Access 2003 standard) or Tabbed Windows (the wave of the future).

4. Click OK.

5. Close and open your database so the new setting takes effect.

For a retro touch, you can use the same setting to make a brand new Access database use overlapping windows instead of tabs.

Creating Another Database

Creating a new database is the easiest task yet. You simply need to choose Office button → New. Access takes you back to the Getting Started page, where you can create a blank database by clicking the familiar Blank Database button, as described earlier (Section 1.2.1).