Software development in Microsoft Access is relatively simple and almost anyone can do it but if you want to be taken seriously by professional database developers then there are some mandatory rules that you must follow whilst developing your database. One of the first aspects professional developers will look at when reviewing your work will be the naming standards you are using for your Microsoft Access Objects. Microsoft Access allows you to use, what is considered in the professional database development world, poor naming standards. For example in your field names, you can have spaces however in the professional world that is a major no no.
One of the reasons we don't use spaces in Microsoft Access is that when you start creating complex queries and functions that refer to fields, if you have spaces in the field names it is possible to put two spaces into the field name but it only appears that there one space. What this means is that your query won't work and can sometimes take many days to find the mistake. The key issue is that you must never use spaces in your field names, database names or any of the seven different object types. There are in fact two ways that you should be naming your fields, database names and so on.
Naming Fields
Let us say for instance you wanted to create a field to store the Postcodes for the suburbs your customers live in. We could write this field in two ways, the first thing you must do for fields is to add the prefix fld at the start of the field. Then add the field name as required -
fldPostcode or fldPost_code
Either technique is quite acceptable. If you were developing a field for Post Codes, that is codes on posts, then the recommended way for writing the field name would be -
fldPostCode or fldPost_Code
You will notice that in the second example the second word is in capitals rather than in lower case. The use of capital letters signifies that each word represents a separate aspect of the field, so in this case the code in capitals means you are referring to codes on posts. The same naming principles apply to database names, and each of the seven Microsoft Access object types.
Naming Tables
When naming tables, there are three prefixes that you can use. The first prefix tbl is used for the core tables you will be storing your good data in. The tmp prefix is used for tables that will be storing temporary data. It is also recommended that you import your data into a temporary table before inserting the data into your good data tables. We do this for two core reasons; the first is that testing your data in a temporary table is much easier than doing it on the fly while you are trying to import your data.
The third table prefix I recommend being used is bck for backup tables. The key advantage of using these prefix's on your tables is that Microsoft Access will automatically group them by the prefix, which means you will keep all your good tables together, your temporary tables together and your backup tables together.
Naming the Seven Access Object Types
When you are working with the seven different object types that make up Microsoft Access, each of the objects has its own prefixes. A list of those prefix names you should be using for the Access Object Types are shown below -
db - Database
tbl - Tables
qry - Queries
frm - Forms
rpt - Reports
mcr - Macros
mdl - Modules
One of the key reasons 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 first and then the queries because t comes before q.
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 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 fldPostcode and the control we are using for this field is a text box, then we would name the text box txtPostcode. Below is a list of prefixes for the forms and report objects.
frm - Forms
rpt - Reports
lbl - Lables
txt - text boxes
cmd - command buttons
lst - List Boxes
cmb - combo boxes
opt - option buttons
ole - ole objects
chd - Child Objects (Subforms or subreports)
For a full list of all prefix names that you should be using in Microsoft Access Development is shown below:
Microsoft Access Objects
db - Database
tbl - Tables
qry - Queries
frm - Forms
rpt - Reports
mcr - Macros
mdl - Modules
Table Objects -
tbl - Core Data Store
tmp - For temporary Tables
bck - Tables that you have backed up
Forms & Reports Objects -
frm - Forms
rpt - Reports
lbl - Lables
txt - text boxes
cmd - command buttons
lst - List Boxes
cmb - combo boxes
opt - option buttons
ole - ole objects
chd - Child Objects (Subforms or subreports)
Report Names -
rpt - For General Reports
cht - For Chart Reports
lbl - For Label Reports
Variables -
str - strings
ole - ole object types
int - integers
dec - decimal
dte - dates
Module Objects -
sub - Subroutines
fn - Private Functions
pfn - Public Functions
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.
Chris Le Roy is a professional software developer and has been developing software applications for over 20 years in Visual Basic, VB .net, VBA, C++, Microsoft SQL Server, Transact SQL, ASP, PHP and Microsoft Access. He is the Managing Director of One-on-One Personal Computer Training that trains over 2000 people per year across Australia and Overseas from basic Microsoft Office functionality to advanced Database Design and VBA. You can access some of his many training manuals and programs by visiting his website - http://www.1-on-1.biz or at his online shop