List of Errors, Suggestions, and Performance Tips

Total Access Analyzer detects over 250 types of errors, suggestions, and performance tips to help you create Access applications that have fewer errors, are easier to maintain, run efficiently, and behave the way your users and you expect. 

Many of these issues are problems we and our customers have experienced over the years. By incorporating such analysis into Total Access Analyzer, you have an automated tool that helps you adopt the industry's Best Practices for building better Access applications.

The analysis is based on the examination of your entire database looking at each object and its relationship to others. For instance, the only way to suggest a query isn't being used is to be able to document all the places queries are used. The thorough and automated way Total Access Analyzer examines all your objects, controls, properties, and lines of code is impossible to replicate manually.  

A quick review of the types of issues detected by Total Access Analyzer should show you why so many Access users and developers rely on Total Access Analyzer to help them build and deploy better Access applications. After all, why embarrass yourself and give your users grief by shipping a database that Total Access Analyzer can tell will crash?

• Errors Detected
• Suggestions Offered
• Performance Tips

These are serious problems that will most likely cause your Access application to crash. When Total Access Analyzer detects these errors, you should examine them carefully and fix them before deploying your database. In fact, many development teams require using Total Access Analyzer as a quality assurance check before deploying any Access application.

Reports Set to a Specific Printer

A common and nasty problem. The report is hard-coded to your specific printer and works fine -- on your machine! Move it to another machine and the report fails. Every report should be set to the default printer and respect the user's Windows printer preferences.

Modules without Option Explicit

Modules without Option Explicit may have undefined variables, misspelled variables, and a variety of other type consistency issues the compiler won't flag. All modules should specify "Option Explicit" so VBA can catch such problems.

Modules without Option Compare

Security Holes

Users defined without passwords are flagged.

Broken Code References

Even when your modules compile, there may be broken references to your code. Functions called in queries, form and report property sheets, and macros all escape detection by the compiler. Renaming functions, misspelling function names, or changing the number of parameters will cause a crash when the user invokes them. Total Access Analyzer detects:

• References to Undefined Functions
• Query Function Calls with Incorrect Number of Parameters
• Form Function Calls with Incorrect Number of Parameters
• Report Function Calls with Incorrect Number of Parameters
• Macro Function Calls with Incorrect Number of Parameters

Broken Object References

References to objects that were deleted or renamed will fail when the user encounters them. These references are "hidden" within your application and very hard to detect. The database may compile fine, but if a form references a table that no longer exists, that's a time-bomb ready to explode. There are many places Access references objects, and Total Access Analyzer documents and verifies them:

• Relationship Reference to Undefined Table or Field
• Query References to Invalid Tables/Queries
• Query References to Invalid Fields
• Form References to Undefined Tables
• Form References to Undefined Fields
• Form References to Undefined Forms
• Form References to Undefined Macros
• Form References to Undefined Command Bars
• Form References to Broken Hyperlinks
• Form References to Undefined Procedures
• Report References to Undefined Tables
• Report References to Undefined Fields
• Report References to Undefined Macros
• Report References to Undefined Command Bars
• Report References to Undefined Reports
• Report References to Broken Hyperlinks
• Report References to Undefined Procedures
• Macro References to Undefined Tables
• Macro References to Undefined Queries
• Macro References to Undefined Forms
• Macro References to Undefined Reports
• Macro References to Undefined Data Access Pages
• Macro References to Undefined Macros
• Macro References to Undefined Functions
• Macro References to Undefined Command Bars
• Macro References to Undefined Views
• Macro References to Undefined Data Diagrams
• Macro References to Undefined Stored Procedures
• Module References to Undefined Tables
• Module References to Undefined Queries
• Module References to Undefined Forms
• Module References to Broken Hyperlink
• Module References to Undefined Reports
• Module References to Undefined Data Access Pages
• Module References to Undefined Macros
• Module References to Undefined Command Bars
• Module References to Undefined Views
• Module References to Undefined Data Diagrams
• Module References to Undefined Stored Procedures
• Command Bar References to Undefined Command Bars
• Command Bar References to Undefined Procedures

Corrupt and Broken Objects

Sometimes objects in Access get corrupted. This is cumbersome to detect in large databases, but Total Access Analyzer finds them easily:

• Container Open Errors
• Table Relationship Window Problems
• Table Open Errors
• Table Index Open Errors
• Table with Invalid Links
• Table Record Count Unavailable
• Query Open Errors
• Query Errors Reading Parameters
• Query SQL Syntax Errors
• Form Open Errors
• Report Open Errors
• Macro Open Errors
• Data Access Page Open Errors
• Security Could not Document Permissions
• Temporary objects that may indicate database corruption

Modules Events Defined by Not Raised

A class defines an event but doesn't raise one. The VBA compiler doesn't care, but you probably would.

Functions and Get Statements Don't Return a Value

Top

Suggestions incorporate our knowledge of Best Practices for creating professional Access applications. Suggestions cover items that are not going to cause your application to crash or perform poorly, but may make it easier to maintain, support, and appear more polished. For instance, we may note that certain objects or code are unused, but that doesn't mean you have to delete them.

Field Data Type and Size Inconsistency

One of the most basic rules of database design is to use the same field name to represent the same data. Assuming you're doing this, the next step is to make sure identically named fields are defined the same way (same data type and size). Failure to do this could cause data loss as information is moved from one table to another. For instance, a FirstName text field should be the same length anywhere it's defined. Similarly, a numeric CustomerID field should be consistent and not an Integer in one table, a Long Integer in another, and a Double in another.

Total Access Analyzer examines all the fields in your database, compares identically named fields in multiple tables, and flags the field names that are not identically defined.

Unused Objects

Ever wonder whether you should delete an old query but was afraid because you weren't really sure if it was used? Over time, many databases get full of unused objects. By documenting all the places an object is used, Total Access Analyzer can flag the objects that aren't used:

• Unused Tables
• Unused Queries
• Unused Macros and sub-macros

Of course there are limitations to any detection of unused objects. Maybe you reference the object in code in a way that can't be detected, or you simply use a table that isn't referenced anywhere else. The analysis isn't a recommendation to delete the object, but rather information that the object isn't referenced by the other documented objects.

Unused Code

Over time, you may also accumulate unused code:

• Unused Procedures
• Unused Variables
• Unused Procedure Parameters
• Unused Constants
• Unused Label (often indicates a broken error handler)
• Unused Classes
• Unused Class Property
• Unused Enum
• Unused Enum Element
• Unused User Defined Type
• Empty Procedures
  Common for form and report events that didn't get filled in
• Procedures that can be moved into another module because they are only called from the other module are identified.

Simplify your modules and reduce bloat by getting rid of code you don't need.

For unused procedure parameters, the analysis is trickier. You can simply eliminate the variable you pass to the procedure, but maybe the problem is deeper. Maybe the procedure should be doing something with that value and behaving accordingly.

Access Options

• "Themed Form Controls" setting is off
• "Always Use Event Procedures" setting is off
• MRU File List should be larger
• "Confirm Document Deletions" setting is off

Module Unreferenced Variables

A powerful feature in the Access 2003 version flags variables that are assigned but not used. For instance, you assign a value to X, then never use X. This could either be code you should eliminate, or code you need to fix because you should be using the value.

Form Design Issues

Total Access Analyzer detects many things to improve your forms:

• Controls Not In Default Tab Order
  It may be what you explicitly want or it may be a mistake.
• Duplicate HotKeys (& + Letter)
  Don't use the same hotkey letter on more than one control
• No Caption Defined
  Do you really want the form name to appear in the title bar?
• ComboBoxes with LimitToList set to No and no NotInList event
  Why have a lookup list if users can enter any value they want?
• System Gray Color Not Used
  New versions of Windows display a system gray color that differs from previous versions. Sections and controls that use the specific gray color rather than the system gray color appear darker. These are now flagged.
• AutoResize not set
  Let the form adjust to its size, not the size you last saved it.
• AutoCenter not set
  Let the form appear in the center of the screen, not where you last saved it.
• ComboBoxes with AutoExpand set to No
• Forms with controls that should have an associated label
• Form option groups that have duplicate values
• Controls that have different shortcut keys for the same control
• Controls that use a space as a shortcut key
• Labels that have a shortcut key, but no associated control
• Combo boxes that display the default number of rows. Displaying more rows allows users to see more items at the same time.

There are also form settings which if not set properly give users more options than you may want them to have:

• Design Changes in view mode
  This should only be available in design mode
• Users Can Switch Views
  Should they be able to switch between Form, Datasheet, PivotChart, and PivotTable views?
• Shortcut Menu set without Specified Menu
  Without an explicit menu, you allow the built-in menu which may or may not be what you want your users to use.

Form Code Issues

Total Access Analyzer also detects coding issues in forms:

• Command Buttons with No Actions
  Buttons without a OnClick event need to be checked.
• Undefined Event Procedure
  The property setting for the control says there's an event, but there's no corresponding code

Report Design Issues

Total Access Analyzer detects many things to improve your reports:

• NoData Event is Missing
  Are you handling situations when there's no data?
• No Caption
  Do you want to show the user the name of the report?
• Grouping's Keep Together Property Set to No
  Will a heading appear at the bottom of the page without any detail records? This should be set to First Detail or Entire Group.
• Undefined Event Procedure
  The property setting for the section says there's an event, but there's no corresponding code
• AutoCenter not set
  The report should preview in the center, not where you saved it.
• Reports with controls that should have an associated label, but do not.

Table Primary Key Issues

• Tables with primary keys not in the first set of fields
• Tables with more than one field as their primary key
• Tables that use non-numeric fields as their primary key
• For MDBs, tables that do not use autonumber field as their primary key
• For ADPs, tables that do not use Identity fields as their primary key

• Multiple indexes on the same field
• Tables with Field Unicode Compression Disabled
• Table Yes/No fields are not displayed as check boxes
• Table or Query uses invalid fonts
• Table, Query, and Field Names conflict with Jet or SQL Server reserved words and future reserved words 
• Table fields that display Combo boxes with the default number of rows. Displaying more rows allows users to see more items at the same time.

Module: Code without Error Handling

Error handling is critical to professional applications. If code without error handling crashes, your users see the default Access debug dialog. That's not good. Ideally, every procedure traps for errors and handles them gracefully either within itself or by passing it to a global error handler.

• Procedures without Error Handling
• Properties without Error Handling

If you find your application is riddled with code missing error handling consider our Total Visual CodeTools program and its Code Cleanup feature that can automatically add custom error handling to the procedures and properties that lack it.

Module: Avoid Name Conflicts

Don't use the same name for variables in the same scope. Variable naming conventions often solve this (e.g. global variables prefixed with a "g", module variables with an "m", etc.). However, if they aren't used, there may be conflicts. Total Access Analyzer flags:

• Variables that Conflict with a Module Variable
• Variables that Conflict with a Global Variable
• Variables that Conflict with a Reserved Word
• Procedures that Conflict with a Reserved Word

Module: Scoping Procedures and Variables

Good programming scopes objects to just the level they're needed. If something is only used in a procedure, it should be defined there. If it's only used in a module, it shouldn't be global to the entire project. Total Access Analyzer flags scoping issues and asks you to explicitly scope your code to make it easier to manage and minimize conflicts:

• Procedures That Should be Private
  Save memory by making procedures private if they're only called from its module
• Module Procedures Should be Explicitly Scoped
  Is it Public or Private?
• Not Specifying DAO or ADO Method
  Recordsets, Fields, Properties, Tables, etc. that are in ADO and DAO should be explicitly defined to its library like DAO.Recordset to avoid problems associated with the order of library references.
• Constants Should be Explicitly Scoped
• Variables Should be Explicitly Scoped
• Enum Should be Explicitly Scoped
• User Defined Type Should be Explicitly Scoped

Module: Typecast to Specific Data Type

If variables aren't explicitly defined as a particular type (e.g. As Integer), they are variants which are less efficient and prevents the compiler from catching certain problems.

• Untyped Functions (return value type not explicitly defined)
• Untyped Parameters
• Untyped Variables
• Untyped Constants
• Module ReDim Variables Should be Explicitly Defined
• Use CDate rather than CVDate
  This casts a date variable rather than a variant
• Other explicit VBA string functions

Other Module Suggestions

• Class Variables Should be Property Statements
• Stop Statements
• Debug Statements

VBA Configuration Suggestions

These options simplify your code writing experience:

• Auto Indent setting should be On
• Auto Syntax Checks should be Off
• Require Variable Declaration should be On

Top

Performance tips are ideas where you may be able to enhance the speed of your application. Certainly, many tips are not relevant because you've already implemented the most efficient solution.  Nonetheless it's helpful to consider these ideas or objects that may be made more efficient.

Database

• Database Linked Tables Optimization
• Database MDE/ADE Test
• Database Module Code Compiled
• Database Split Database Test
• Database Workgroup File Current

Computer

• System Access/Office Installation
• System Free Disk Space
• System Processor Type
• System RAM Level
• System Windows Installation

Tables

• Table Contains No Primary Key
• Table Relationship Participation
• Table Contains a Large Number of Fields
• AutoIndex on Import/Create should be set to blank so indexes don't get created automatically
• Tables with SubDatasheet Name Property Value [Auto] are Listed  (the SubDatasheet Name Property Value Should be [None])
• Table is linked to an older database format

Queries

• Query uses slow Domain functions
• Query Returns a Large Number of Columns
• Query Select Syntax
• Query Slow Count Function
• Query Unconstrained (no Where clause)
• Query maintains a transaction log

Forms

• Form OLE Object Controls
• Form Contains a Large Number of Controls
• Form Control Cost
• Form RecordSources should be a saved query
• Form uses slow Domain Functions

Reports

• Report Contains a Large Number of Controls
• Report OLE Object Controls
• Report RecordSources should be a saved query
• Report uses slow Domain Functions

Macros

• Macro uses slow Domain Functions

Modules

• Module Object Variables
• Module Variant Variables
• Module Unreferenced Varables
• Module uses slow Domain Functions
• Module Find Methods
• Module IIF Functions
• Module Requery Action

ADP Views

• View Returns a Large Number of Columns