As you work with a database, it can become fragmented so that it uses disk space inefficiently. Compacting the database rearranges the way the database file is stored on the disk so that it takes up less disk space and works more efficiently.

In addition, it’s possible that your database can become damaged or corrupted, although this is not a common occurrence. Typically, a damaged database will behave unpredictably. For example, a search may not display any records, new records may not appear, or deleted records may still show up in a table. In some cases, Access will inform you that your database is damaged when you try to open it.

To compact and repair the current database:

1. Select Tools | Database Utilities | Compact and Repair Database.
2. Access will compact and repair the database.

You can also compact and repair a database that is not open. For this to work, you must not have any database open. Also, when you compact and repair a database that is not open, Access will create a copy of the database and compact and repair the copy. The original will not be compacted and repaired.

To compact and repair a database that is not open:

1. Make sure that no database is open.
2. Select Tools | Database Utilities | Compact and Repair Database.
3. Access will display the following dialog box:



4. Select the database you want to compact. Change drives and/or folders, if necessary.
5. Click the Compact button.
6. Access will display the following dialog box:



7. Type a new name for the database. Change drives and/or folders if necessary.
8. Click the Save button.
9. Access will create a copy of the database, using the name you specified, then compact and repair the new database.

TIP: When Should You Compact a Database?

It’s a good idea to compact your database after you delete information.

Here are some general tips for designing and maintaining databases from one of our experts:

1. When developing forms and reports, focus on functionality first. Once things are working correctly, then you can make them look pretty.
2. When designing tables to hold contact information, try to break information down into its parts.
   Separate name into FN, MI, LN, and Address into Add1, Add2, City, State, Zip, Country.
   It’s easy to put things together afterwards if necessary, not so easy to break them apart.
3. Always back up files (the whole .mdb file, or specific forms, reports, etc.) before making changes to them.
4. On the database menu, Tools>Startup has useful options like setting a form to open automatically, and hiding the database window on open.

When database development is finished, split the database, putting tables in the “back-end” database, and forms, reports, queries, etc in the “front-end”.

Just open the database and go to Tools>Database Utilities>Database Splitter, and follow the instructions.

Splitting improves speed, reliabilty, and convenience, especially in a multi-user environment.