Access 2010: The Missing Manual

Saving Databases

Unlike other programs, Access doesn’t require that you save your data. It automatically saves any edits you make to the records in a table. This automatic-saving process takes place every time you change a record, and it happens almost instantaneously. It also 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 any change to your data is saved the moment you make it.

Note

The rules are a bit different for database objects (Understanding Access Databases). When you add or edit a database object, Access waits until you finish and close the object, at which point it prompts you to save or discard your changes. If you’re a bit paranoid and you can’t stand the wait, just choose File→Save to save the current database object immediately.

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-11). 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-11. The Undo command appears in the Quick Access toolbar at the top left of the Access window, so it’s always available.

For these reasons, it’s a good idea to make frequent database backups. To make a database 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. First, choose File→Save & Publish. Then, under the File Types heading, choose Save Database As. Finally, under the Save Database As heading, double-click Back Up Database. This opens to create a copy of your database, in the location you choose (Figure 1-12).

Figure 1-12. When you choose to create a backup, 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.

Of course, it’s still up to you to remember to copy your database backup to another location (like a network server) or to a different type of storage (like a DVD or a USB memory stick), so you’re ready when disaster hits. You can use any number of different backup tools to safeguard your computer, including the Backup and Restore Center included in Windows Vista and Windows 7.

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 .laccdb to track who’s currently using the database. The l stands for lock, and it’s used to make sure that if more than one person is 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).

You’ll learn more about how Access works with multiple users in Chapter 19, but for now it’s safe to ignore the .laccdb file. You don’t need to include it in your backups.

Saving a Database with a Different Name or Format

You’ve probably already noticed the File→Save Database As command. When you click it, you can save your database with a new name. With this command, however, you’re limited to the standard .accdb format used in Access 2010 and Access 2007, which isn’t compatible with older versions of Access. If you try to open Bobblehead.accdb in Access 2003, you’ll get nothing more than a blank stare and an error message.

Instead, Access 2003 uses the .mdb file format (which stands for Microsoft database). And, as you can see in Figure 1-13, the .mdb format actually comes in three versions: the really, really old original format, a retooled version that appeared with Access 2000, and the improved-yet-again version that Microsoft introduced with Access 2002 and reused for Access 2003.

To save the current database using an older Access file format, you have to use File→Save & Publish, and then, under the File Types heading, click Save Database As. The standard .accdb format is the best choice if you don’t need to worry about compatibility, because it has the best performance and a few extra features. But if you need to share databases with people running much older versions of Access, the .mdb format is your only choice.

Keep in mind that once Access creates the new database file, that file is 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 need to use File→Save Database As to save it again.)

Figure 1-13. To change the format of your database, choose File→Save & Publish (1), click Save Database As (2), and then pick the format you want from the “Database File Types” section (3). Use “Access 2002-2003 Database” or “Access 2000 Database” to save a .mdb file that works with an older version of Access.

You can also use the old-style .mdb format when you first create a database. Choose File→New and then click the folder icon next to the File Name box. Access opens the File New Database dialog box (which you saw back in Figure 1-2). It includes a “Save as type” box where you can choose the Access 2002-2003 file format or the even older Access 2000 format. (If you’re set on going back any further, say the Access 95 format, your best bet is a time machine.)

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 File→Info and click the big Compact & Repair Database button. Access then closes your database, compacts it, and opens it again. If it’s a small database, these three steps unfold in seconds. 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.

Note

If you compact a brand-new database, Access shows a harmless security warning when the database is reopened. You’ll learn about this message, and how to avoid it, in the next section.

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 File→Options to get to the Access Options window.

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

4. Under the Application Options heading, turn on the “Compact on Close” checkbox.

5. Click OK to save your changes.

   Access tells you that this change has no effect until you close and reopen your database.

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.