Avoiding Microsoft Access Maintenance Nightmares
There is a greater need to adopt a Code Naming Standard with Microsoft Access, than with any other programming language or database system. This is because a reference to an object can be so easily confused.
Is the object referenced a Table or a Query or a Field or a Macro or a Text Box or a Combo Box or a Variable or a Module or a Form or a Report?
All these objects can have the same name in Microsoft Access – and the Access system easily copes with the ambiguity. It will just use the first reference that it finds.
By more luck than judgement the database may work as intended. But maintaining the database will be a nightmare. Just change the name of a Field or Form or Combo Box and the Access project is likely to become unstable. Note that the Name AutoCorrect option may not change all references.
Enforce a Code Naming Standard
These difficulties can be avoided by adhering to a Code Naming Standard. The Coding Standard should:
- Be consistently applied
- Remove all ambiguity in named objects
- Make the coding easy to read and easy to understand
- Make it easy to references any object
The result will be
- A system that is easily maintainable
- Changes, enhancements and bug fixes will be easy to do
- The system will become more reliable
- The Programmer will become more effective
VBA Object Naming conventions
These are the conventions that I use when programming a Microsoft Access database:
Variables do not need to have a prefix, as long as the meaning and the type of variable is obvious from the name used
Public and Private Variables
Variables declared in the General Declarations section of a module should have a scope prefix of "m_" (module scope) or "g_" (global scope)
This is more understandable than the "blnOpen" naming convention
The variable name should always end with "Date"
The currency type is obviously implied
objDatabase As DAO.Database
The object name should always have a prefix of "obj" before the object name.
Indicates that the field "Input" has an unknown type
The naming convention is the same as for a variable
Const COLNAME As Integer = 3
The constant should be capitalised
The collection should be prefixed with "col"
NameArray() As string
Using the word "Array" avoids confusion with procedure parameters
Database object names should not contain any spaces
The "Customer" Table prefix will prevent ambiguity between Tables
"qry" should prefix the query's description
Programmers who use the default of Text1 should be pilloried!
'-- Still to be tested
All Procedures and complex statements must have comments explaining the concept and intent
"rpt" should prefix the report's description
"mac" should prefix the macro's description
It is only necessary to use the prefix of "lbl" when the label is referred to in code
"frm" should prefix the form's description
The Procedure name should describe the purpose of the Procedure
"mod" should prefix the module's description
"cls" should prefix the Class's description
"cmd" should prefix the command's description
For a full list of prefixes for ActiveX Controls, see Microsoft's Visual Basic Control Naming Conventions
Code Naming Standards: Summing it all up
Adhering to a Code Naming Standard requires little effort. Trying to enforce a Standard some years after creation requires massive effort. Deleting or renaming fields in a Table can easily break an Access database system.
A Code Naming Standard is essential in making your Access Database projects easy to read, more reliable and easily maintainable.