Microsoft Access Errors, Suggestions, and Performance Tips

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

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

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 behaving accordingly.

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

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:

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:

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.

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

• 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