Provided by Allen Browne, June 2006. Last updated: 8 May 2010
This free utility (160 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.
Configure the utility to skip certain checks, avoid checking for particular words and characters, and add your own words/characters to the lists:
Main screen of the utility:
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, dbOpenDynaset, dbSeeChanges)
|Home||Index of tips||Top|