Preventing Database Problems

In the day-to-day use of an Access database-adding and deleting records, modifying forms and reports, and so on-various problems can develop. This is especially true if the database is stored on a network share, rather than on a local drive, and is accessed by multiple users. Access monitors the condition of database files as you open and work with them. If a problem develops, Access attempts to fix it. If Access can’t fix the problem, it usually provides additional information that might help you to find a solution. But Access doesn’t always spot problems before they affect the database; if this happens, you might notice that the database performance seems to slow down or become erratic. Even if no actual errors occur, normal database use causes the internal structure of a database to become fragmented, resulting in a bloated file and inefficient use of disk space.

You don’t have to wait for Access to spot a problem. There are various things you can do to help keep your database healthy and running smoothly. Your first line of defense against damage or corruption in any kind of file is the maintenance of backups. Database files rapidly become too large to conveniently back up onto a floppy disk, but you have many other options: you can copy the file to another computer on the network or to removable media such as a USB flash drive, send it as an e-mail attachment to another location, create a tape backup, or burn a CD-ROM.

Tip 

To back up a database in Access 2007, click the Microsoft Office Button, point to Manage, and then click Back Up Database.

You can use the following Access utilities to keep your database running smoothly:

• Compact and Repair Database. This utility first optimizes performance by rearranging how the file is stored on your hard disk, and then attempts to repair corruption in tables, forms, reports, and modules.

• Performance Analyzer. This utility analyzes the objects in your database and offers three types of feedback: ideas, suggestions, and recommendations. You can instruct Access to optimize the file by following through on any of the suggestions or recommendations.

• Database Documenter. This tool produces a detailed report containing enough information to rebuild the database structure if that were ever necessary.

• Analyze Table. This wizard tests database tables for compliance with standard database design principles, suggests solutions to problems, and implements those solutions at your request.

• Microsoft Office Diagnostics. This command attempts to diagnose and repair problems with your Microsoft Office programs.

Important 

Take care when running the Microsoft Office Diagnostics utility, because it might change files and registry settings that affect all Office programs.

In this exercise, you will back up a database and then run the Compact And Repair Database, Performance Analyzer, and Database Documenter utilities.

Use the 09_Prevent database. This practice file is located in the Chapter12 subfolder under SBS_Office2007.

Open the 09_Prevent database. Do not display any of the database tables.

1. Click the Microsoft Office Button, point to Manage, and then click Back Up Database.

2. In the Save As dialog box, navigate to your Documents\MSP\SBS_Office2007\ Chapter12 folder, and then click Save.

Tip 

When you back up a database, Access appends the current date to the database file name in the following format: 09_Prevent_2007-04-22.accdb. You can change the file name to suit your needs.

Access creates a compacted copy of the database in the specified folder.

3. Click the Microsoft Office Button, point to Manage, and then click Database Properties.

The Database Properties dialog box opens, displaying information about your database on five tabs.

4. On the General tab, note the size of the database. Then click OK to close the dialog box.

5. Click the Microsoft Office Button, point to Manage, and then click Compact and Repair Database. Acknowledge the safety warning if prompted to do so.

The utility takes only a few seconds to run, and you will see no difference in the appearance of the database.

Troubleshooting 

If you don’t have enough space on your hard disk to store a temporary copy of the database, you don’t have appropriate permissions, or another user also has the database open, the Compact And Repair Database function will not run.

6. Repeat Steps 3 and 4 to display the database size, and compare it to the original database size.

You can expect a 10 to 25 percent reduction in the size of the database if you have been using it for a while.

Tip 

It is a good idea to compact and repair a database often. You can have Access do this automatically each time the database is closed. To do so, click the Microsoft Office Button, click the Access Options button, and then on the Current Database page, select the Compact On Close check box, and click OK.

7. On the Database Tools tab, in the Analyze group, click the Analyze Performance button.

The Performance Analyzer dialog box opens.

This dialog box contains a tab for each type of object the utility can analyze, and a tab displaying all the existing database objects.

8. On the All Object Types tab, click Select All, and then click OK to start the analyzer.

When it finishes, the Performance Analyzer displays its results. (The results you see might be different from those shown here.)

The icons in the left column of the Analysis Results list indicate the category of each entry: Recommendation, Suggestion, and Idea. (After you perform any of the optimizations, Fixed entries will also appear in the list.) Clicking an entry displays information about it in the Analysis Notes section.

9. Scroll through the list, clicking each entry in turn, and read all the analysis notes. Most of the suggestions are valid, though some, such as the one to change the data type of the PostalCode field to Long Integer, are not appropriate for this database.

10. Close the Performance Analyzer dialog box.

11. On the Database Tools tab, in the Analyze group, click the Database Documenter button.

The Documenter dialog box opens. This dialog box is identical to the Performance Analyzer database. It contains a tab for each type of object the utility can document, and a tab displaying all the existing database objects.

12. On the Tables tab, click the Options button.

The Print Table Definition dialog box opens.

The dialog box offers print options associated with the objects on the selected dialog box tab. The options differ for each tab, but all are similar to these, in that you can use them to specify the documentation to include for each type of object.

13. In the Print Table Definition dialog box, click Cancel.

14. On the All Object Types tab, click Select All, and then click OK to start the documentation process.

Tip 

You can’t save the report generated by the Documenter utility, but you can export it as a Word RTF file, Access database, text file, XML file, or HTML document. To do so, right-click the report, point to Export, and then click the format you want.

When the process finishes, Access displays a report in Print Preview. This report can run to hundreds of pages, so you probably don’t want to print it. However, it is a good idea to create and save a report such as this for your own databases, in case you ever need to reconstruct them.

Close the Object Definition report and the 09_Prevent database. If you are not continuing directly on to the next chapter, quit Access.