9 Tools
9.1 About tools
Access has several tools to perform additional work on databases. This chapter deals with the following:
- Analysis of a table
- Compressing database
Other tools in Access are:
- Analyze performance
- Documenting database
- Encrypt with a password
- Creating a switchboard
9.2 Analyze table
Purpose: Investigation of inconsistency in tables.
When the same information is stored more than once in different tables, this is called redundancy. That is undesirable because when the information changes, you need to change this on all those places. If you don’t then the database is inconsistent, the information on these sites is contradictory.
Access has a tool Analyze Table to detect redundant data and to split a table with redundant data in multiple, related tables so that the information is stored more efficiently. This process is called normalization.
You can specify which tables you want to the wizard creates for you or you can let the wizard normalize your table. After this, the wizard helps to sanitize the data in the original table. In the last step, you can create a query that returns all information in the splitted tables, displayed in a single datasheet that matches the original table.
In Figure 9.1 you can see an example of a table Contacts with many duplicated data in it. So, this table is not normalized. Access can split this table into two tables so that as little data as possible is stored multiple times.
Task 9.1 Practice file: tools.accdb
Open the practice file.
Select table Contacts.
Choose tab Database Tools > Analyze Table (group Analyze). The wizard Table Analyzer appears with some general explanation of duplicate information.
Click Next. A new screen of the Table Analyzer wizard appears with a general explanation about how this problem can be solved.
Click Next. Now the wizard asks you which table has redundant data.
Select table Contacts and click Next. Now you can specify who decides about the choice for the fields, the wizard, or you.
Select that you want to make the decisions. Then click Next.
In this step, you can create new tables and add fields.
Select field ContactName and drag it out of the table. A new table is created that contains the field name ContactName. The wizard asks now for the name of the new table.
Enter Contactpersons as the name for the new table and click OK.
There is also a relationship created between the two tables.
The location and dimensions of the displayed tables can be modified by dragging the table itself or the edges.
- Drag now the fields CompanyName and Tel to the table Contactpersons.
Click Next. The wizard asks now if you want to create a query.
Don’t create a query and click Finish. There are now 3 tables: Contacts (the original table), Contactpersons (new table), Tabel1 (new table).
There may be a warning window stating that the command or action TileHorizontally isn’t available. If so, click OK.
Close the tables and when prompted, then save the modified designs.
Remove the original table Contacts.
Rename Tabel1 in Contacts.
9.3 Compact and repair databases
How to defragment and repair databases.
Multiple problems can occur in a frequently used database where you add, modify, and delete records. Deleting records or objects in a database can cause defragmentation of the database, making the database inefficient. When you open a database, Access examines its condition and tries to fix problems. But not always the problems are discovered and in then the user had to start a compact and repair action.
Compact also affects autonumbering in an Access database. If you have deleted records at the end of a table with an Autonumber field, the AutoNumber value for that field is reset. It will then start at the next higher number when you add new records.
To manually compact and repair a database choose tab Database Tools > Compact and Repair Database (group Tools).