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 local area network and is accessed by multiple users. Access monitors the health of database files as you open and work with them. If it sees a problem developing, it attempts to fix it. If Access can’t fix a problem, it usually displays a message suggesting that you take some action. But Access doesn’t always spot problems before they affect the database, and sometimes database performance seems to slow down or become erratic. Even if no serious errors creep in, simply using a database causes its internal structure 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 to floppy disk, but you have many other options: you can copy the file to another computer on the network, send it as an e-mail attachment to another location, use a tape backup, burn a CD-ROM, or copy it to some other removable media.
There are several utilities that you can use to keep your database running smoothly. The following list describes a few of these utilities:
Compact and Repair Database. The repair portion of this utility attempts to repair corruption in tables, forms, reports, and modules. Compacting the database rearranges how the file is stored on your hard disk, which optimizes performance.
Performance Analyzer. This utility analyzes the objects in your database and offers feedback divided into three categories: ideas, suggestions, and recommendations. If you would like to follow through on any of the suggestions or recommendations, you can click a button to have Access optimize the file.
Documenter. This tool, which is part of the Performance Analyzer, produces a detailed report that can be saved and printed. It includes enough information to rebuild the database structure if that were ever necessary.
Detect and Repair. This command, which appears on the Help menu, is not a command to be clicked casually. Running this utility might make changes to files and registry settings that affect all Office programs.
In this exercise, you will back up the GardenCo database, and then compact and repair it. You will then run the Performance Analyzer and Documenter.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSSwitchbrdHealth folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the File menu, click Back Up Database.
In the Save Backup As dialog box, navigate to the My DocumentsMicrosoft PressAccess 2003 SBSSwitchbrdHealth folder, accept the file name Access provides, and click Save.
Tip
When you create a back up file, Access appends the current date to the file name in the following format: GardenCo_2003-04-22.mdb. You can change the file name to suit your needs.
A copy of the database is created and stored in the specified folder.
Acknowledge the safety warning to reopen the GardenCo database.
On the File menu, click Database Properties to open the dialog box.
This dialog box contains five tabs that display information about your database.
Click the General tab, and note the size of the database.
Click OK to close the dialog box.
On the Tools menu, point to Database Utilities, and then click Compact and Repair Database, acknowledging the safety warning when prompted.
The utility takes only a few seconds to run, and you will see no difference in the appearance of the database.
Display the database’s Properties dialog box again, and compare the current size to its previous 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.
On the Tools menu, point to Analyze, and then click Performance.
This Performance Analyzer dialog box is displayed:
This dialog box contains a tab for each type of object that can be analyzed, and a tab that displays objects of all types.
Click the All Object Types tab.
Click Select All, and then click OK to start the analyzer.
You will see quite a bit of action on your screen as the analyzer opens and closes windows. (If the splash screen is open, the analyzer skips it.) When it finishes, the analyzer displays its results in the dialog box. (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, Idea, and Fixed. (When you first run the Performance Analyzer, there will be no Fixed entries in the list.) Clicking an entry displays information about it in the Analysis Notes section.
Scroll through the list, click each entry in turn, and read through 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.
Close the Performance Analyzer dialog box.
On the Tools menu, point to Analyze, and then click Documenter.
The Documenter dialog box appears. It is similar to the Performance Analyzer dialog box, in that it contains a tab for each object type that it can document. You can select individual objects on one or more tabs, or click All Object Types and make your selections.
Click the Tables tab, and then click the Options button.
The Options dialog box offers print options associated with the current tab. The options differ for each tab, but all are similar to these, in that you can use them to specify what to include in the documentation for each type of object.
Click Cancel to close the dialog box.
Click the All Object Types tab.
Click Select All, and then click OK to start the documentation process.
As the documenter runs, objects are opened and closed, and the status bar displays the progress through the objects. When the process is finished, a report is displayed in Print Preview.
This report can run to hundreds of pages, so you probably don’t want to click the Print button right now. However, it is a good idea to save a report such as this for your own databases, in case you ever need to reconstruct them.
You can’t save the report generated by the documenter, but you can export it. On the File menu, click Export, and then select a format. The best format is probably RTF, which can be opened in Microsoft Word.
Close the report.
CLOSE the GardenCo database.