Naming Conventions.
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 quicker and easier. The naming convention will also make life easier when revisiting a database to add new functionality, which maybe in a few months or even years time.
The following rules for naming objects in your Access Database should always be observed. These apply to the naming of everything within your database from tables, queries, forms, reports, macros and VBA modules to field names within tables and controls within forms and reports: (not following rules 1 and 2 can cause errors within your database, the other rules are optional but very highly recommended)
1 Do Not Use Spaces or Special Characters
If you have never done any type of development or programming before you may be tempted to use spaces or special characters when naming database objects. This can make it difficult (or produce errors) when referring to them in queries or VBA. The use of spaces or special characters will end up causing you problems. Always avoid them when naming objects in your database.
Special characters / \ | @ £ ^ ( ) [ ] { } ; : ! # & = * + - ? " ' $ %
2 Do Not Use Reserved Words
Reserved words are words that are reserved for use by built in Access functionality or SQL functionality. The most common reserved words that new developers use when naming database objects are: Date, Day, Month, Year; so for example a developer may have a field in the Sales table called ‘Date’ this should be avoided and should be called something like ‘SaleDate’.
3 Keep Names Short yet Informative
A table or query etc… needs to have a name that is informative but not too long or too short. For example a query named ‘qryListOfSalesForEachDepotGroupedByMonth’ should be called something like ‘qryMonthlySalesByDepot’ and should definitely not be called ‘qryMSD’.
4 Use Title Case also known as CamelHump
A query named ‘qryMonthlySalesByDepot’ is easier to read than a name written in all lower or upper case i.e. ‘QRYMONTHLYSALESBYDEPOT’ or ‘qrymonthlysalesbydepot’
5 Use Name Prefixes
The following are a list of prefixes and example names to use when naming objects. By using a prefix you can 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 key 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 always 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 don't 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 often have a situation where the wrong control is referred to. 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 the query is used for and also to gather like types together.
eg. qryFrmClients Main Form
qryFrmSubClients Sub Form
qryRptClients Report
qryRptSubClients Sub Report
Notice the use of CamelHump. This will sort the queries into like groups and also distinguish a query from a table.
Forms follow the same convention and so do Reports.
Eg. 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 Fields in Tables. Usually singular
City
SaleDate
FirstName
LastName
Adding prefixes can become confusing and really don’t add any value.
Primary Keys and Foreign Keys.
Foreign Keys should share the same name with the Primary Key.
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 suffix of ID serves little except to confuse.
Naming Fields in Queries.
Queries use the table’s field name.
Calculated Fields need naming and should observe the basic naming convention of short, simple and descriptive.
Form and Report Control Objects.
Text Boxes txt
Labels lbl
Command Buttons cmd
Combo Boxes cbo
Toggle Buttons tgb
Option Buttons opb
Option Groups opg
List Boxes lst
Images img
Unbound Object Frame uof
Bound Object Frame bof
Page Break pgb
Tab Controls tab
Sub Forms sbf
Sub Reports sbr
Lines lin
Rectangles rct
Variables
Strings str
OLE object types ole
Integers int
Decimal dec
Dates dte
Module Objects
Subroutines sub
Private Functions fn
Public Functions pfn
Using the naming standards shown above will assure that if you do require assistance from a professional database developer that they will take you far more seriously than before because you have shown a professional understanding on how to name your database object. This will certainly give you credibility in the eyes of professional database developers.
