Compacting Your Database

As you delete old database objects and add new ones, the space within your .accdb file can become fragmented. The result is that, over time, your database file can grow larger than it needs to be to store all your definitions and data.

To remove unused space, you should compact your database periodically. No other users should be accessing the database you intend to compact. You can compact the database you currently have open by clicking the Microsoft Office Button, clicking the Manage command, and then clicking Compact And Repair Database. If you want to compact another database, you must close your current database and then click the Compact And Repair Database command again. Access 2007 opens the dialog box shown in Figure 5–41.

Figure 5–41: Click the Microsoft Office Button, Manage, and then Compact And Repair Database to open the dialog box for specifying a database to compact.

Select the database you want to compact, and then click Compact. Access asks you for a name for the compacted database. You can enter the same name as the database you are compacting, or you can use a different name. If you use the same name, Access warns you that the original database of the same name will be replaced. If you proceed, Access compacts your database into a temporary file. When compaction is successfully completed, Access deletes your old database and gives its name to the new compacted copy.

Inside Out-Compacting a Database When You Close It 

You can also set an option to compact the database each time you close it. Open your database, click the Microsoft Office Button, and then click the Access Options button. In the Access Options dialog box, select the Current Database category and then select the Compact On Close check box under Application Options. If multiple users are sharing the same database, Access compacts the database when the last user closes it.

You now have all the information you need to modify and maintain your database table definitions. In the next chapter, you’ll explore importing data from other sources and linking to data in other files.