Visual Basic Programmer

Visual Basic Programmer

Access Database Programmer.
Visual Basic.Net Programmer.
Microsoft Office Programmer.


VB Programing support
Access Database
Microsoft Office
VB.Net & SQL Server




nev@NevVB.com.au



Ring me for Visual Basic and Access programming
Sydney, Australia
(02) 9453-0456




Contact Details



28/01/2012

Microsoft Access Database Programmer

Avoiding Access Maintenance Nightmares

Microsoft Access Database Coding StandardsThere 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
  • Programming 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 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.


Home Page         Next Page