Maintaining Access Databases

Many SQL Server 2000 database maintenance tasks have equivalent actions in Access databases. Similar to SQL Server 2000, important Access database maintenance tasks include documenting your databases and backing up your databases. Access also provides tools such as the Table Analyzer Wizard, the Performance Analyzer, and the Compact And Repair command that you can use to optimize your Access database (.mdb) files.

Documenting Access Databases

To document an Access database, on the Tools menu, point to Analyze and then click Documenter. Select the check boxes next to the database objects that you want to document, and then click OK. A series of reports that document the structure of the database’s objects is created. The reports can be printed or saved to file formats such as Excel workbooks, HTML, Rich Text Format, or Access Snapshot Format by clicking Export on the File menu after Access creates the reports.

In the Documenter dialog box, click the Option button to include in the reports information such as table properties, relationships, and permissions; field names, data types, sizes, and properties; and index names, fields, and properties.

To thoroughly document a database, click the All Object Types tab and then click the Select All button. Be aware that the more objects you select, the longer it takes to generate the final report and the larger the final report becomes. For example, documenting the sample Northwind database can generate a report of more than 600 pages.

Backing Up and Restoring Access Databases

Access does not include any built-in database backup utilities. To back up an Access database, first close the database. (If you are using the Access database in a multiuser environment, confirm that all users have closed the database.) Then, using Microsoft Windows Explorer, the Microsoft Windows 2000 Backup And Recovery Tools, the Windows XP Backup Or Restore Wizard, or other backup software, copy the Access database to a backup medium of your choice. How you restore an Access database from a backup copy depends on which method you used to produce the backup copy. Using Windows Explorer or another backup tool, copy the backup Access database to your database folder.

If the Access database you are restoring and the backup copy have the same name, restoring the backup can replace the existing file. If you want to save the existing file, you should rename it before you copy the backup database. You can also back up individual database objects by creating a blank database and exporting the objects you want from the original database to the blank database by clicking Export on the File menu.

Caution

If you are using user-level security, you should also create a backup of the workgroup information file. If you accidentally lose or damage the workgroup information file, you won’t be able to start Access again until you restore or rebuild the workgroup information file. User-level security is covered in more detail later in this chapter in the section “Securing Access Databases.”

Note

Replicating a database is not the same operation as backing up a database. You replicate a database when you want to synchronize data in several copies of the same database across multiple computers. However, if a replica of a database is damaged and an undamaged backup of the database is not available (especially the master database replica), the damage could propagate through the connected databases with few or no recovery options. Database replication can be a somewhat complex subject, especially for new Access database administrators. For more information on replicating Access databases, see the Access online help.

Using the Table Analyzer Wizard

You can use the Table Analyzer Wizard to reduce duplicate data in Access database tables. Although the Table Analyzer Wizard doesn’t tell you which tables need to be analyzed, the wizard can help you split data tables to create new tables in which each piece of data is stored only once. This allows you to update data in one place and use the data many times. The nice thing about using the Table Analyzer Wizard is that your original data table remains unchanged, which allows you to play with the wizard’s settings until you get the results you want. To use the Table Analyzer Wizard, on the Tools menu, point to Analyze, click Table, and proceed through the wizard’s steps.

Using the Performance Analyzer

The Performance Analyzer optimizes Access databases, recommending tasks that you can carry out automatically as well as making other suggestions that involve tradeoffs you must agree to before you carry out the suggested tasks. Finally, the Performance Analyzer can provide optimization ideas that you must carry out yourself. To use the Performance Analyzer, on the Tools menu, point to Analyze, click Performance, and proceed through the Performance Analyzer’s steps.

You can practice using the Performance Analyzer to analyze the sample Northwind database. When you start Performance Analyzer, click the All Object Types tab, click the Select All button, and then click OK. Performance Analyzer suggests that you create an MDE file (a more secure copy of the databases), use fewer controls on the Employees and Orders forms, convert macros to Visual Basic code, and so on.

Compacting and Repairing Databases

If you delete data or objects from an Access database, you can leave gaps that the data or objects once occupied. These gaps result in inefficient disk space usage and longer data search times, especially with large files. Compacting Access files rearranges the files’ contents to reduce or eliminate these gaps. You should regularly compact Access files for improved performance.

In most cases, Access detects whether an Access file is damaged when you try to open it; Access then gives you the option to repair the file before Access continues to try to open the file. If the damaged file contains a reference to another damaged Access file, Access does not attempt to repair the other damaged file. In some cases, Access may not detect that a file is damaged. If an Access file behaves unpredictably, you should compact and repair the Access file at the same time.

To compact and repair an Access file, on the Tools menu, point to Database Utilities, and then click Compact And Repair Database. Access closes the file, compacts and repairs the file, and then reopens the file.

To help prevent Access files from being damaged, you should

• Compact and repair Access files regularly.

• Back up your Access files regularly to prevent having to deal with damaged files.

• Avoid quitting Access unexpectedly (for instance, by suddenly turning off your computer by using the power switch).

• Compact and repair all your Access files after rebooting your computer should your computer shut down unexpectedly.

Securing Access Databases

You have several options for securing an Access database and its associated objects (tables, forms, queries, and so on). Encrypting a database makes a database’s data indecipherable by software applications such as word processors. Encryption is particularly useful when you transmit a database electronically. To encrypt an Access database, start Access but don’t open any database. Point to Security on the Tools menu, click Encrypt/Decrypt Database, and then follow the directions displayed on your screen. Repeat these steps to decrypt an Access database by selecting a file with the extension .mde or .ade.

Encrypting an unsecured database has no effect, however, because anybody can open the database and gain full access to the data. One way to secure an Access database is to require a password to open the database file. After a password is set, a dialog box requesting the password is displayed whenever the database is opened. Only users who enter the correct password are allowed to open the database. After the database is open, all the database’s objects are available to the user unless other types of security have been defined, as described later in this section. For a database that is shared among a small group of users or for a database on a single computer, setting a password is often all the security that is required.

To set a password for an Access database, you first need to open the database for exclusive use. Use the Open command on the File menu to open the database. In the Open dialog box, click the arrow beside the Open button and then click Open Exclusive. Then, on the Tools menu, point to Security, click Set Database Password, enter and confirm the password, click OK, and then close the database. To unset the password, open the database for exclusive use, enter the database password, and then click OK. With the database now open, point to Security on the Tools menu, click Unset Database Password, enter the password, click OK, and then close the database.

A more secure option is to implement Access user-level security. User- level security allows you to establish different levels of access to sensitive data and objects in a database. Users need a password to open the database, and then Access reads a workgroup information file in which each user is associated with a unique ID. The combination of unique ID and password determines which data and objects a user can access. The easiest way to implement user- level security is to point to Security on the Tools menu in Access, click User- Level Security Wizard, and then step through the wizard’s pages.

For help completing the Security Wizard, click the Help button on any of the wizard’s pages. For more information about Access security options, see the Access online help.