MS Access Column Names - Using the Right Naming Conventions for Columns in Access
Introduction
There are two aspects to naming conventions for MS Access columns. The first is just the names we use as headers on columns when a report is printed out from the Access database. Very generally speaking, these headers should be meaningful. Looking at the column head, one should be able to understand, or at the very least make a guess, as to what the data printed/displayed under that column name represents. The second aspect of the convention relates to the programming variables/database elements. There are some conventions that have been in use for quite sometime and should therefore be used. It is not important which one as long as one of them is used. These conventions have specified how to name the columns/fields in an Access table/record.
Naming Convention Background
Leszynski/Reddick naming conventions are the most widely-used naming conventions for use in developing MS Access applications. Lyszenski and Reddick parted ways later, but Reddick went on to develop what is known as RVBA convention. This is the Reddick VBA convention standard. The naming conventions defined by these standards cover every element used during development. Everything from a variable to the name of a field is defined in these standards.
The general structure is to use some unique tags and some suffixes along with the names of the elements so that they can be easily identified as to what type of element it is. This is true whether the actual element name is meaningful or meaningless. Just to make sure even the element names are meaningful, one could follow some guidelines. While generally true, we would look at these guidelines as far as they apply to field names. Name a field so that one can easily identify what it is. So, if the field in the table represents the home address of an employee in the record, name it employee_address. (Why that underscore is used rather than a blank will be discussed later.)
One should be using the minimum number of words to describe a field. For the same employee record a “JoiningDate” or Joining-Date field is more appropriate than “Date on which employee joined.” Avoid the use of anything that can cause confusion. Thus, abbreviations or a field may have values which can indicate more than one thing. It is better to have separate fields for phone number, cell number, and fax number rather than a combined phone/cell/fax field.
Another general convention with either convention is that you should not use a blank in the name, and the entity/column/field names should not start with a number. Blanks are to be avoided as querying the database can become difficult because it becomes very difficult to make out if the name has one or more blanks. In case of a mismatch in the numbers, the query will fail. If the field names are to be meaningful, they should not start with a number. In both cases, if the actual name is made of more than one word, they are each written with a capital letter first or “camel backed.” The date of joining would look like tagJoiningDate.
You can also use an underscore to joint the different parts of the name. For example, tagJoining_Date also is an acceptable variation of the naming. Here the “tag” is the appropriate tag according to the convention you are going to use. Another common convention with either standard is not to use special characters. Both conventions are similar, but you should follow just one of them.
Leszynsky Naming Convention (LNC) and RVBA
We should be absolutely clear that Access does not impose any restrictions about naming of fields/columns or any other element. Following conventions increases readability and the self-documenting nature of the programs developed in Access. If you were to call in expert help at some point in the development of your project, it will be far easier to understand for the external expert if a convention was followed. Within the team, it will make communication very effective and save time during project meetings when development strategies or problems are discussed.
When naming each of the entities, there is a tag added to the name. Tag comes first followed by the name. The tag is a unique two/three letter combination for the type of entity. The tag “tbl” is a good tag when tables are to be used. The “tmp” tag defines temporary tables. Using a “bck” tag is useful for backup tables. A secondary benefit is that Access will keep the tables with the same starting tag together. Thus, the main table, the temporary table, and the back up tables remain together.
The seven types of objects in Access have tags: “db” for database, “tbl” for tables, “qry” for queries, “frm” for forms, “rpt” for reports, “mcr” for macros, and “mdl” for modules. Fields or column names have tags depending on the data type they are supposed house. These are “bin” for binary, “byt” for byte, “cur” for currency, “dtm” for date/time, “dbl” for double precision data, “lng” for long integer, “mem” for memo, “ole” for OLE objects, “sng” for single, “str” for text strings, and “ysn” for yes/no type fields/columns.
These examples are from the LNC. However, the conventions from RVBA also are similar; in these specific cases of column names, there are only slight variations in the tag names.
