Naming Conventions for Microsoft Access

Software development in Microsoft Access isobjects that need to be named include the bound and
relatively simple and almost anyone can do it but if youunbound controls found on forms and reports. When
want to be taken seriously by professional databaseyou create a form or report using the wizard or
developers then there are some mandatory rules thatAutoForm or AutoReport each of the controls are
you must follow whilst developing your database. Onenamed the same as the field names. Now whilst this is
of the first aspects professional developers will look atby default, it is not really an acceptable way of naming
when reviewing your work will be the namingcontrols. One of the key reasons we don't want to do
standards you are using for your Microsoft Accessthis is that sometimes when we are working with
Objects. Microsoft Access allows you to use, what isforms and reports we want to refer to the control
considered in the professional database developmentrather than the field. By having the field names the
world, poor naming standards. For example in your fieldsame as the object names, you can often have a
names, you can have spaces however in thesituation where the wrong control is referred to. The
professional world that is a major no no.One of theway we overcome this, is by naming our controls
reasons we don't use spaces in Microsoft Access isbased on the control type. For example if our field was
that when you start creating complex queries andcalled fldPostcode and the control we are using for
functions that refer to fields, if you have spaces in thethis field is a text box, then we would name the text
field names it is possible to put two spaces into thebox txtPostcode. Below is a list of prefixes for the
field name but it only appears that there one space.forms and report objects.frm - Forms
What this means is that your query won't work andrpt - Reports
can sometimes take many days to find the mistake.lbl - Lables
The key issue is that you must never use spaces intxt - text boxes
your field names, database names or any of thecmd - command buttons
seven different object types. There are in fact twolst - List Boxes
ways that you should be naming your fields, databasecmb - combo boxes
names and so on.Naming FieldsLet us say for instanceopt - option buttons
you wanted to create a field to store the Postcodesole - ole objects
for the suburbs your customers live in. We could writechd - Child Objects (Subforms or subreports)For a full
this field in two ways, the first thing you must do forlist of all prefix names that you should be using in
fields is to add the prefix fld at the start of the field.Microsoft Access Development is shown
Then add the field name as required -fldPostcode orbelow:Microsoft Access Objectsdb - Database
fldPost_codeEither technique is quite acceptable. If youtbl - Tables
were developing a field for Post Codes, that is codesqry - Queries
on posts, then the recommended way for writing thefrm - Forms
field name would be -fldPostCode orrpt - Reports
fldPost_CodeYou will notice that in the secondmcr - Macros
example the second word is in capitals rather than inmdl - ModulesTable Objects -tbl - Core Data Store
lower case. The use of capital letters signifies thattmp - For temporary Tables
each word represents a separate aspect of the field,bck - Tables that you have backed upForms &
so in this case the code in capitals means you areReports Objects -frm - Forms
referring to codes on posts. The same namingrpt - Reports
principles apply to database names, and each of thelbl - Lables
seven Microsoft Access object types.Namingtxt - text boxes
TablesWhen naming tables, there are three prefixescmd - command buttons
that you can use. The first prefix tbl is used for thelst - List Boxes
core tables you will be storing your good data in. Thecmb - combo boxes
tmp prefix is used for tables that will be storingopt - option buttons
temporary data. It is also recommended that youole - ole objects
import your data into a temporary table beforechd - Child Objects (Subforms or subreports)Report
inserting the data into your good data tables. We doNames -rpt - For General Reports
this for two core reasons; the first is that testing yourcht - For Chart Reports
data in a temporary table is much easier than doing itlbl - For Label ReportsVariables -str - strings
on the fly while you are trying to import your data.Theole - ole object types
third table prefix I recommend being used is bck forint - integers
backup tables. The key advantage of using thesedec - decimal
prefix's on your tables is that Microsoft Access willdte - datesModule Objects -sub - Subroutines
automatically group them by the prefix, which meansfn - Private Functions
you will keep all your good tables together, yourpfn - Public FunctionsUsing the naming standards
temporary tables together and your backup tablesshown above will assure that if you do require
together.Naming the Seven Access Objectassistance from a professional database developer
TypesWhen you are working with the seven differentthat they will take you far more seriously than before
object types that make up Microsoft Access, each ofbecause you have shown a professional
the objects has its own prefixes. A list of those prefixunderstanding on how to name your database object.
names you should be using for the Access ObjectThis will certainly give you credibility in the eyes of
Types are shown below -db - Databasetbl -professional database developers.Chris Le Roy is a
Tablesqry - Queriesfrm - Formsrpt - Reportsmcr -professional software developer and has been
Macrosmdl - ModulesOne of the key reasons wedeveloping software applications for over 20 years in
name our tables and queries with a prefix is that whenVisual Basic, VB .net, VBA, C++, Microsoft SQL
you are working with these two object types in theServer, Transact SQL, ASP, PHP and Microsoft
query window, Microsoft Access simply lists all tablesAccess. He is the Managing Director of One-on-One
and queries together without differentiating them. ByPersonal Computer Training that trains over 2000
using the tbl and qry prefixes on these two objects, itpeople per year across Australia and Overseas from
always ensures that the tables are shown first andbasic Microsoft Office functionality to advanced
then the queries because t comes before q.OtherDatabase Design and VBA.