Microsoft Access Errors, Suggestions, and Performance Tips
Best Practices Detected by Total Access Analyzer
Total Access Analyzer detects 300 types of errors, suggestions, and performance tips to help you create Microsoft 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, our automated tool helps us adopt the industry's Best Practices for building better Access applications.
The analysis is based on the examination of your entire database by looking at each object and its relationship to others. For instance,
the only way to suggest a query isn't being used is to document all the places queries are used, and determine the ones left over. 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?
These are serious problems that will most likely cause your Microsoft 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.
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
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 Control Name
- 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 Control Name
- 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
- Command Bar References to Undefined Command Bars
- Command Bar 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
Form has Timer Event but No Timer Interval
The form has a Timer event but the form's timer interval property is set to zero. That means the Form_Timer procedure
This may be okay if you set the timer interval programmatically, but if not, you may want to delete it or comment it out.
Form has Timer Interval but No Timer Event
The form's timer interval property is set to a non-zero value, but there isn't a Timer event for the form. That
means nothing occurs when the timer interval is reached.
You should have a Form_Timer procedure to perform a task when
the timer interval is reached, or set the timer interval property to zero.
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 consistency issues the
compiler won't flag.
All modules should specify "Option Explicit" so VBA can catch such problems.
Modules without Option Compare
When the Option Compare statement is missing, Option Compare Binary is assumed, which is a case sensitive
By default, Access uses Option Compare Database which respects the sort order of your database
and performs case insensitive text comparisons.
Since this is so common, it is best to use this, then use the VBA StrComp function with the vbBinaryCompare option
when you want case sensitive comparisons.
Undefined TempVar Reference
A temporary variable is referenced but not defined in the documented objects.
Macro Line has Action with Missing Arguments
A macro action is missing expected parameters and is unlikely to run properly.
Macro RunMenuCommand Action is Missing Command
A macro line has a macro RunMenuCommand action is missing its command.
Functions and Get Statements Don't Return a Value
Functions and Get Statements should return a value. Functions that are not assigned a return value should generally be defined as Subs
instead of Functions.
Property Get statements that are not assigned a return value will cause a runtime error when called.
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.
Users defined without passwords are flagged.
Suggestions incorporate our knowledge of Best Practices for creating professional Access applications. Suggestions
cover items that will not 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.
Ever wonder want to delete an old query but were 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 Forms
- Unused Reports
- Unused Macros and sub-macros
- Unused Embedded 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.
Combined with the Search feature, you can quickly find whether the object name is being used by your object properties, macro lines, and VBA code.
Of course there are some situations that Total Access Analyzer cannot
detect. If the object is used interactively, part of a table driven
system where the object name is stored in a table, referenced by
variables in code, you'll need to be cautious as a static analysis
program like Total Access Analyzer cannot detect those uses. Here is
more information on how Total Access Analyzer detects unused objects in Microsoft Access.
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 DLL and Windows API Declare Procedures
- Unused Enum
- Unused Enum Element
- Unused User Defined Type
- Empty Procedures
Common for form and report events that didn't get filled in
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
Here is more information on how Total Access Analyzer detects unused Microsoft Access VBA code.
Field Data Type and Size Inconsistency
One of the fundamental Best Practices of database design is to use the same field name to represent the same data. Identically named
fields should be defined the same way with the same data type and size. Failure to do this could cause data loss as information moves 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. Here's a sample report from the Field Consistency Analysis:
Identical SQL in Queries, Record
Sources, and Row Sources
Total Access Analyzer performs advanced analysis on your SQL strings to see if duplicates exist across your objects. Duplicates are detected
even if you have slight differences with spacing, line breaks, etc.
Duplicate SQL strings are opportunities to eliminate queries or create a saved query to share across objects. The benefit is the ability
to adjust the SQL in one place and have all its dependencies use it. The downside is that a future change for one purpose may trigger a problem
elsewhere. Here's a sample report from the Identical SQL Suggestion:
Form Design Issues
Total Access Analyzer detects many things to improve your forms:
- **Incorrect Link to Subforms
The LinkMasterFields of a subform should be to a control on the master form and not directly to a field in the formís recordsource. This lets the subform
refresh when the user edits the controlís value rather than waiting for the record to be saved. Total Access Analyzer detects this mistakes and
offers a new suggestion to when itís discovered. See our paper
Microsoft Access Subforms Should Reference the Control Rather than Field for Master Link Fields for details.
- 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.
- Combo boxes 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.
- Forms with the Moveable property set to No
- Forms with unnecessary scroll bars
- Datasheets should use a Navigation Caption
In Access 2007, you can add a custom caption so the default "Record" is replaced by a more meaningful term.
- CommandButtons should have Hover Hand turned on
This new feature in Access 2007 changes the mouse cursor to a pointing hand when it hovers over a button. This makes the user experience
similar to an internet browser, and lets you create transparent buttons that behave like hyperlinks.
- Datasheet Should Have Alternating Colors for Rows
In Access 2007, a simple property setting lets you display datasheets with color on alternating rows. This makes it much easier to read multiple records on a grid.
- Datasheet Alternate Colors for Rows Conflicts with Access
In Access 2007, if you set alternate row colors that do not match the Access setting
Navigation Controls were introduced in Access 2010:
- * Navigation Control Button Has No Action
A navigation control has a navigation button that does not have any events and its NavigationTargetName property is not defined. This means if the user clicks
on the button, the navigation subform is not updated and nothing happens.
- * Navigation Control Button has No Caption
A navigation control button doesn't have a caption or picture, so it's just blank.
- * Navigation Control Button Should Show Hyperlink Hand
Display a hyperlink hand rather than the default mouse arrow when the cursor is over the button. This provides a user experience similar to a web browser. Set the
button's Cursor On Hover property to Hyperlink Hand to get this feature.
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.
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
Table/Query Design Issues
- 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.
- Avoid using InputMasks on date fields since this should be controlled by the Format property and date input is already validated by Access during data entry.
- Avoid using OpenQuery to run an action query
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.
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
Cross-Reference and Field Type Comparison
Total Access Analyzer generates field cross-reference for the relationship joins and makes sure they are defined with
the same data type.
If not, a suggestion is offered to review the inconsistency.
- Themed Form Controls setting should be On
- Always Use Event Procedures should be On if you don't write macros for form and report events
- The Most Recently Used (MRU) File List should be set to the maximum
- Confirm Document Deletions should be On to prevent accidental deletions
- Design Changes for Tables in Datasheet View should be Off
Users shouldn't be able to change table structures interactively
- Window Options should be Tabbed Documents
Take advantage of the ability to open objects as separate tabs. Combined with the Anchoring of controls, this lets you create sizable displays
and provides a more Internet/modern experience for users.
- Navigation Pane's Search Bar should be On
This is the most redeeming feature of the Navigation Pane, so to not use this feature would be a shame.
- Allow Shortcut Menus Should be On
If the database's Allow Default Shortcut Menu option is turned off, it prevents users from right clicking on a table, datasheet, or form
view to use the Access menu to filter or sort their list. If there are specific views where you do not want this feature, you can turn it off
on the form, but this setting at the database level prevents all objects in your database from having this feature.
- Check for Truncated Number Fields
Microsoft Access has an option to display numbers as "#####" when a column is too narrow to display the entire value. If the Check for
Truncated Number Fields option is not selected, the values visible in a column are truncated based on what can fit in the column. This can
be misleading since only a portion of the digits are displayed. You can set this under Access Options, Current Database, Check for Truncated Number Fields.
- Add Color to Your Application
The instance of Office uses the default color scheme of Silver. Try the Blue option for a more colorful end user experience. In Access,
this is set under File, Options, General, User Interface options. Note that this is set at the Office level for the user and not within your
database. Your database should support whatever selection the user chooses for their color theme.
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
- * Property or Procedure Name Conflicts with its Parameter
Parameters shouldnít be the same name as the property or procedure name since it creates confusion when referencing it.
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
Module: Exit with the Body of a Procedure/Property
If an Exit statement (Exit Sub, Exit Function, or Exit Property) exists in the body of a procedure (rather than at the bottom), any code
below it is skipped.
This can be a problem if the following code is necessary to cleanup things started by the procedure. It's also a
problem if the procedure is very complex and the Exit statement is buried in the middle somewhere. Subsequent developers (or yourself) may
forget this behavior and spend unnecessary time trying to debug the code.
A better way to exit a procedure is to set variables and use IF statements or other conditionals so that the process always exits at the
bottom of the procedure. Consistently using this Best Practice makes your work easier to maintain and debug.
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
- Procedures That Can be Private if Moved
If a public procedure is only called from one module, it can be moved to that module and made private
- 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
While unused variables are relatively easy to resolve by eliminating the variable, unreferenced variables can indicate deeper problems.
Analyzer flags variables that are assigned but not used. For instance, you assign a value to X, but never use X.
This could either be code you should
eliminate, or code you need to fix because you should be using the value.
Temporary variables that are defined but not referenced are detected.
Form Code Issues
Total Access Analyzer also detects coding issues in forms:
- Command Buttons with No Actions
Buttons without an 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
Module: Avoid Using Old Access Constants
Constants that are part of the OldConstants module in the Access library are from Access 95 or earlier. Examples include A_COPY, A_DIALOG, A_FORM,
Total Access Analyzer detects these and recommends the "modern" equivalent such as acCopy, acDialog, acForm, etc.
Other Module Suggestions
- Class Variables Should be Property Statements
- Stop Statements
- Debug Statements
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.
- 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
- Query with Grouping Based on Query with Grouping
A query has a GroupBy clause and uses a query with a GroupBy clause. If the grouping can be performed just once, performance may be improved significantly.
- 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
- 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
- Report Contains a Large Number of Controls
- Report OLE Object Controls
- Report RecordSources should be a saved query
- Report uses slow Domain Functions
Macro uses slow Domain Functions
- Module Object Variables
- Module Variant Variables
- Module Unreferenced Variables
- Module uses slow Domain Functions
- Module Find Methods
- Module IIF Functions
- Module Requery Action
- Database Linked Tables Optimization
- Database MDE/ADE Test
- Database Module Code Compiled
- Database Split Database Test
- Database Workgroup File Current
- Set Access Option for Picture Property Storage Format to store graphics in a smaller size
- System Access/Office Installation
- System Free Disk Space
- System Processor Type
- System RAM Level
- System Windows Installation
* Added in Total Access Analyzer 2013 (New 2013 Features)
** Added in Total Access Analyzer 2016 (New 2016 Features)