Avoiding 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 Textbox 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.
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:
| 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 Control Naming Conventions
To sum 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.