Microsoft Access: Applications and Utilities

Provided by Allen Browne, June 2006, updated February 2008

Database Issue Checker Utility

This free utility (130 KB zipped) reports on potential issues with the structure of Access databases. It makes no changes to the databases you examine.

The utility is intended for developers to check their application during design, and consultants who examine others' databases.

Some of the issues it identifies may be irrelevant - even intended - in your application. For example, null foreign keys can be a very useful design technique, and spaces in a field name will not cause problems beyond slowing development. The utility merely reports the facts, and you decide what is relevant. You can therefore configure it to skip particular words or characters, and ignore issues.

February 2008 update: Reports on Name AutoCorrect, even if it has never been set. Skips [Switchboard items] table.
March 2007 update: Fixed a bug testing Allow Zero Length; Added a test for Layout View.

What it does

Issues identified:

1. Reserved words and other problem words used as the names of tables, fields, and queries. (View the list of 2500+ words.)
2. Problem characters in the names of tables, fields, and queries.
3. Tables that cannot be opened (e.g. attached tables with bad links).
4. Tables without a primary key.
5. Relationships without referential integrity enforced.
6. Foreign key fields that are not required (permitting orphan records).
7. Name Autocorrect options set (confusing/corrupting the database).
8. Datasheet Schema option set (users can change the structure in a datasheet - Access 2007).
9. Tables with Subdatasheet Name set (performance issue).
10. Fields with Allow Zero Length property set (performance and integrity issue).
11. Fields of type Decimal (Access bugs).
12. Complex data fields (applicable to accdb and accdt files, not mdb.)
13. Record too wide (too many characters in a table/query if all fields were filled.)
14. Layout view permitted (Forms and reports allow changes with data - Access 2007 only.)

Limitations:

• Requires Access 2000 or later.
• Does not work with password-protected databases or secured databases.
• Designed for JET tables (not tables attached from data sources other than Access.)
• Does not test the names of query fields and parameters.
• Does not suggest where relationships should be (examines existing relations only.)
• List of reserved words was created from Access 2007 Beta 2 (may not reflect all names in final release.)
• Provided as is, without warranty or support.

Using the utility

To use:

1. Download and unzip DbIssueChecker.mdb.
2. Open with Microsoft Access. The main form opens.
3. Click the Folder button to select the file you wish to examine.
4. Check the boxes for the issues to test for.
5. Click the Examine button, and read the report.

Configuring the utility

Configure the utility to skip certain checks, avoid checking for particular words and characters, and add your own words/characters to the lists:

• For each issue listed above, you can suppress checking for that issue by unchecking a checkbox.
• The list of reserved words is stored in table tblBadWord. You can add other problem words to this table, or configure the utility to suppress reporting on individual words by checking the Skip box in the table.
• The list of problem characters is stored in table tblBadChar. You can add other characters to this table, or configure the utility to suppress reporting on particular characters (such as spaces in names), by checking the Skip box in the table.

Screenshot

Main screen of the utility:

Behind the scenes

If you want to trace the logic behind how it works:

• frmMain is the main interface, with frmIssue as a subform where the user can chose the issues to examine.

• tblIssue lists the issues the utility can examine, with a description and help for each one. Do not change the IssueID values: this primary key is a bitfield, so the numbers can be summed and the code can read each bit to determine the issues to check for.

• tblLogDb logs each database you examine, including the bitfield value of what was examined, and how many issues were reported.

• tblObject lists each object in the last database you examined. This table is self-joined, so can identify which objects belong to which (e.g. which table a field belongs to.) The object types are identified in lookup table ltObjectType.

• tblObjectIssue is a junction table between tblObject and tblIssue, listing the issues identified for each object.

• qryObjectList is a UNION query that gets the object names from two instances of tblObject, assembling them so each object is associated with the correct parent (if any). qryResult takes that result, grabs the data from the other tables, and feeds the report rptResult.

• The code in module ajbIssue clears out the result tables, performs the checks and writes the results. ExamineDatabase() is the main routine, with the specific checks and writes handled by separate routines. The code is designed so it can work independently of the interface.

• To use this utility with SQL Server tables, change each instance of:
      OpenRecordset(strSql)
  to:
      OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)