MS Access Naming Conventions (2 Viewers)

Status
Not open for further replies.

RainLover

VIP From a land downunder
Local time
Tomorrow, 04:59
Joined
Jan 5, 2009
Messages
5,041
_________________________________________________________

Naming Conventions.

This document is aimed at the user who is unfamiliar with any naming convention.

It is based upon personal experience and the interaction I have had with others.

It is not a hard and fast rule.


Why use a Naming Convention?

Using a naming convention when creating your Access Database is vital. A good naming convention will help stop errors that can occur due to badly named objects and will make the initial development of your project quicker and easier. A good naming convention will also make it easier when revisiting a database to add new functionality. This revisit could be after a considerable amount of time. It is then that you will appreciate the structure you employed during the initial development phase of the Database.

Some Basics.

Normalisation is a topic that is widely discussed. If you are not aware or at least have a basic understanding of the subject then you should make yourself familiar. There are many discussions available on the WWW. Normalisation is the foundation stone of any Access database.

Reserved Words are words that are reserved for use by the built in Access functionality or SQL functionality. Examples of the most common reserved words that new developers use when naming database objects are: Date, Day, Month, Year, Now, Print. A developer may have a field in the Sales table called ‘Date’. This reserved word ‘Date’ should be replaced with something like ‘SaleDate’.
A full list of reserved words can be found by searching the WWW.

Spaces and Special Characters. You may be tempted to use spaces or special characters when naming database objects. These special characters can produce errors when referring to them in queries or VBA if not handled correctly. Avoid them when naming objects in your database. The underscore “_” is a widely accepted character by many programmers. It is not recommended here, but simply acknowledged to be acceptable.
Special characters / \ | @ £ ^ ( ) [ ] { } ; : ! # & = * + - ? " ' $ %

Keep Names Short yet Informative. A table, query, form, report etc. needs to have a name that is informative. Not too long and not too short. For example a query named ‘qryListOfSalesForEachDepotGroupedByMonth’ should be called something like ‘qryMonthlySalesByDepot’ and should definitely not be called ‘MSD’.

Title Case also known as CamelHump. A query named ‘qryMonthlySalesByDepot’ is easier to read than a name written in all UPPER CASE or all lower case i.e. ‘QRYMONTHLYSALESBYDEPOT’ or ‘qrymonthlysalesbydepot’

Name Prefixes. The following is a list of prefixes and example names to use when naming objects. By using a prefix you can more easily distinguish between different object types that have the same name. I.e. if you have a table named ‘Staff’ and a report named ‘Staff’ it is difficult to tell which is which, so name them ‘tblStaff’ and ‘rptStaff’.
Another reason we name our tables and queries with a prefix is that when you are working with these two object types in the query window, Microsoft Access simply lists all tables and queries together without differentiating them. By using the tbl and qry prefixes on these two objects, it ensures that the tables are shown separately from queries.

Other objects that need to be named include the bound and unbound controls found on forms and reports. When you create a form or report using the wizard or AutoForm or AutoReport each of the controls are named the same as the field names. Now whilst this is by MS Access default, it is not really an acceptable way of naming controls. One of the key reasons we do not want to do this is that sometimes when we are working with forms and reports we want to refer to the control rather than the field. By having the field names the same as the object names, you can have a situation where the wrong object is referred.

The way we overcome this, is by naming our controls based on the control type. For example if our field was called Postcode and the control we are using for this field is a text box, then we would name the text box txtPostcode and the label lblPostcode. Note how we refer to text boxes as a control. It is not a field as many think. There are no fields in Reports and Forms. They are controls.

HIERARCHY OBJECTS
Tables tbl
Queries qry
Forms frm
Reports rpt
Macros mcr
Modules mod, mdl or bas

TABLES.
Tables are usually plural and are preceded with tbl
e.g. tblClients
tblSales
tblCities

QUERIES
Queries are preceded with qry
e.g. qryClients
qrySales
qryCities

For greater clarity one can describe what a query is used for. This will also sort the queries into like groups and distinguish a query from a table. Notice the use of CamelHump.

E.g. qryFrmClients Main Form
qryFrmSubClients Sub Form
qryRptClients Report
qryRptSubClients Sub Report

FORMS and REPORTS
Forms follow the same convention and so do Reports.

E.g. frmClients Main Form
frmSubClients Sub Form
rptClients Report
rptSubClients Sub Report

MACROS are preceded with mcr
MODULES are preceded with mdl or mod. Some programmers of old use bas.

Naming of Fields in Tables. Usually singular
City
SaleDate
FirstName
LastName

Adding prefixes to field names is a practice of old and not often adopted in MS Access.

Primary Keys and Foreign Keys.

Primary Keys should share the same name with its Foreign Keys.
The Primary Key name should be unique within the Database.
The Primary Key should be tagged as the Primary Key and the Foreign Key should be tagged as the Foreign Key.
E.g. ClientPK as Primary Key
ClientFK as Foreign Key.

The commonly used ‘ID’ for both Primary and Secondary Keys does not create a distinguishing identifier.

Naming Fields in Queries.

Queries inherit the table’s field name.
Calculated Fields within queries require their own naming and should observe the basic convention of short, simple and descriptive.

Form and Report Control Objects.

Text Boxes txt
Labels lbl
Command Buttons cmd
Combo Boxes cbo
List Boxes lst
Images img
Sub Forms sbf
Sub Reports sbr

Review

This document is aimed at the user who is unfamiliar with any naming convention. It is based upon my personal experience and the interaction I have had with others.

The naming standard shown above is but one convention. The convention that you adopt is entirely up to you.

Choose a naming convention and stick with it, however be prepared to change it if something better comes along.
 
Last edited by a moderator:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom