Best Practices for Microsoft Access Database Development

Errors, Suggestions, and Performance Tips Detected by Total Access Analyzer

Total Access Analyzer detects over 270 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, we have an automated tool that can 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?

• Errors Detected
• Suggestions Offered
• Performance Tips

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.

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

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.

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.

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.

Unused Objects

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

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.

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

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.

Here is more information on how Total Access Analyzer detects unused Microsoft Access VBA code.

Unreferenced Variables

While unused variables are relatively easy to resolve by eliminating the variable, unreferenced variables can indicate deeper problems. Total Access 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.

Access Options

• 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

* For Access 2007:

• * 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.

* 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:

• 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.
• * 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.
• * Forms with the Moveable property set to No

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.
• * 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.

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
• 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

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: 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, etc. Total Access Analyzer detects these and recommends the "modern" equivalent such as acCopy, acDialog, acForm, etc.

* 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.

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

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
• * Set Access Option for Picture Property Storage Format to store graphics in a smaller size

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