9  Tools

Keywords

Access, databases, tables, queries, forms, reports, macros, textbook

OBJECTIVES

  • Analyze tables and detect data inconsistencies
  • Compact and repair databases to improve performance .

9.1 About Tools

Access provides several tools to help manage and optimize your database. This chapter covers the following:

  • Table analysis
  • Compacting and repairing databases

Additional useful tools in Access include:

  • Performance Analyzer
  • Database Documenter
  • Encrypt with Password
  • Creating a Switchboard

9.2 Analyze Table

Goal: Detecting inconsistencies in tables.

When the same piece of information is stored in multiple places, it’s called redundancy. This is problematic because if that information changes, you must update it in every location. If you don’t, your database becomes inconsistent—meaning the same data exists in contradictory forms.

Access includes an Analyze Table wizard that helps detect redundancy and split a table into multiple related tables for more efficient data storage. This process is called normalization.

You can allow the wizard to decide how to split the data, or you can make those decisions manually. After splitting, the wizard can help you clean up the original data. At the end, you can choose to create a query that merges the normalized tables into a view that resembles the original.

In , you see an example of a table named Contacts with lots of duplicated data—this table is not normalized. Access can split it into multiple tables so that redundant data is minimized.

Figure 9.1: Original Contacts table with redundant data (not normalized).

Task 9.1 Practice file: tools.accdb

  1. Open the practice file.

  2. Select the Contacts table.

  3. Go to Database Tools > Analyze Table (group Analyze). The Table Analyzer wizard opens with an introduction explaining redundant data.

  4. Click Next. The next screen provides information on how the wizard can resolve redundancy.

  5. Click Next again. The wizard asks which table contains redundant data.

  6. Select the Contacts table and click Next. Now you’ll choose whether you or the wizard will decide how to separate the data.

  7. Select the option to make the decisions yourself, then click Next.

Figure 9.2: Table Analyzer Wizard – manual field selection and normalization options.

In this step, you can create new tables and assign fields to them.

  1. Select the ContactName field and drag it outside the current table area. A new table is created containing that field. The wizard prompts you to name the new table.

  2. Name the new table Contactpersons and click OK.

Figure 9.3: New table Contactpersons created by dragging the ContactName field.
  • A relationship is automatically created between the original and the new table.
  • You can adjust the table positions and sizes by dragging them or their borders.
  1. Drag the fields CompanyName and Tel to the Contactpersons table.
Figure 9.4: Final result: fields moved and tables normalized with relationships.
  1. Click Next. The wizard asks if you want to create a query.

  2. Choose not to create a query and click Finish. Now three tables exist:

    • Contacts (original)
    • Contactpersons (new)
    • Tabel1 (new)

You may see a warning message stating that the command TileHorizontally is unavailable. If so, click OK.

  1. Close and save the tables.

  2. Delete the original Contacts table.

  3. Rename Tabel1 to Contacts.

9.3 Compact and Repair Databases

Goal: Defragmenting and repairing database files.

Over time, as you add, edit, and delete records, your database can become fragmented, resulting in slower performance. Deleting records or objects may leave behind unused space. Although Access attempts to repair issues when you open a database, it doesn’t always catch every problem. In such cases, you should manually run the Compact and Repair utility.

Compacting also affects AutoNumber fields. If records at the end of a table with an AutoNumber field are deleted, the next new record will continue numbering from the last used value.

To manually compact and repair a database, go to Database Tools > Compact and Repair Database (group Tools).