Neville
Silverman

Visual Basic Programmer, Sydney
Microsoft Access Database Programmer

Custom Built Software
System Optimisation
Office Automation
Phone Australia
(02) 9453-0456

Avoiding Microsoft Access Maintenance Nightmares

Avoiding Access Maintenance NightmaresThere 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:

Object Type
Example
Comments
Variable
CustomerName
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
g_Connection
Variables declared in the General Declarations section of a module should have a scope prefix of "m_" (module scope) or "g_" (global scope)
Boolean
IsOpen
This is more understandable than the "blnOpen" naming convention
Date
StartDate
The variable name should always end with "Date"
Currency
PaidAmount
The currency type is obviously implied
Object
objDatabase As DAO.Database
The object name should always have a prefix of "obj" before the object name.
Variant
varInput
Indicates that the field "Input" has an unknown type
Property Name
clsEmail.SenderName
The naming convention is the same as for a variable
Constants
Const COLNAME As Integer = 3
The constant should be capitalised
Collection
colCustomer
The collection should be prefixed with "col"
Array
NameArray() As string
Using the word "Array" avoids confusion with procedure parameters
Table
tblCustomer
Database object names should not contain any spaces
Field
CustomerID, CustomerName
The "Customer" Table prefix will prevent ambiguity between Tables
Queries
qryCustomer
"qry" should prefix the query's description
Textbox
txtCustomer
Programmers who use the default of Text1 should be pilloried!
Comments
'-- Still to be tested
All Procedures and complex statements must have comments explaining the concept and intent
Report
rptCustomer
"rpt" should prefix the report's description
Macro
macFunctions
"mac" should prefix the macro's description
Label
lblCustomerName
It is only necessary to use the prefix of "lbl" when the label is referred to in code
Form
frmCustomer
"frm" should prefix the form's description
Procedure
GetCustomerName
The Procedure name should describe the purpose of the Procedure
Module
modStartup
"mod" should prefix the module's description
Class
clsEmail
"cls" should prefix the Class's description
Command
cmdUpdateCustomerTable
"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.