When Naming Objects,

The first purpose of Naming Conventions is to ensure that the names of objects are unique.

Access may allow you to give the same name as a table to a form or report (but not a query) but that doesn’t mean you should. At some point you may need to rename an object or look for occurrences in code. Being unique means that when you search you will only find the references you want.

The second purpose is to make identifying an object’s type easy. For example, if you see an object with a ‘tbl’ prefix then you will know it is a table and not a query.

Naming conventions offer visual clues as to the object type when you are viewing code. Using my rules, it is easy to identify a column name as it has just one underscore.

Example: CustomerContact_ID being the field name as opposed to a variable lngCustomerContactID

The “ID” at the end tells me instantly what data type it is.

The LNC naming convention is the de facto standard for Access. It’s been in use since the early 1990s. This standard dictates that –

Table Names are prefixed with ‘tbl’

Queries with ‘qry’. I often use ‘qcbo’ for queries designed specifically for use with Combo or List Boxes.

Forms with ‘frm’ and sub forms with ‘sfrm’. We can go even further and use ‘ds’ for Forms designed to use in Datasheet view, ‘dlg’ for forms designed to be opened in Dialog Mode.

Reports with ‘rpt’ and sub reports with ‘srpt’

There are reasons why you would want to distinguish between Forms and Sub Forms. For example if you want to log the usage of your forms to see which ones are not being used then you may only want to log the opening of the main form. But then you can look to see if a Sub Form is actually being used by looking to see if it the Source Object in any other forms’s sub form controls.

I’ve gone a bit further with query names where they are used as record sources for forms or reports. Although it may ago against the concept of reuseability, I prefer to build a query that is only used by that interface object. This ensures that any modification to the query to suit that object will not break any other object. For these specialised queries, I use the name of q[ObjectName]

Example: The query used a the records source for frmCustomer is qfrmCustomer. When you do a search in the Access Nav Pane you easily find both the form and it’s record source.

Consistency is Vital

Although there are existing rules that you should follow when you start out, you may, as will some of my examples, modify or improve on the standards.

Regardless of what standard or rules you adopt, the key to efficiency is Consistency. Consistency saves you time in having to do a lookup to find the correct name or data type of an object when you are developing.

Consistency of naming becomes vital when you start writing code that will write code.

Often in applications, I’ll create one form that I’ll then clone and run a function to find and replace the entity name. Example, if I first create a Customer form, then when I’m happy with the way it works, I copy that form and replace each instance of “Customer” with “Supplier”. Big time saver.

Singularity of naming gives greater consistency. For example, some programmers name their tables using a plural eg Users. They do this so that the table name won’t clash with a reserved word like User. However, the English language is inconsistent as to plurals. You can’t simply add a ‘s’ to the end of the name. Examples: Entity.

This rule allows me to write code that simply takes the ‘tbl’ off the front of a table name and add “_ID” to get its Primary Key field name. I do this a lot when I create data classes.

The only time I break this rule is where I have two forms that display the same entity. One displays a single record, the other is a continuous form. In this case I’ll name the former frmCustomer and the latter frmCustomers

Field Naming

When naming fields your objectives are –

1. Avoid using a reserved word (like Date or Name) as a field name. If your field names include at least two words then you will avoid this issue. Using a reserved word means that you will need to surround your field names with square brackets so that SQL knows that you are referring to a field name and not a function. In the two examples I gave – Name can refer to the name of the object (eg form or report) and so Access may mistake your field name for the name of the object. Date is a build in function so using a field name like  ‘Date’ could be ambiguous. So using at least two words and no spaces avoids ambiguities without the need for square brackets.
2. Don’t use spaces. If you use spaces then you will need to surround your field names with [square brackets] so that Access and SQL statements know where the field name begins and ends.
3. Avoid using special characters (like single or double quotes, % or *) or arithmetic operands (like + or /). These can confuse SQL Statements and It doesn’t take a lot of imagination to guess what kind of issues will result from using arithmetic operands. Stick to Letters, Numbers and Underscores.

When naming fields I use an underscore followed by a data description. For example Birth_Date. I immediately know when looking at this field name that it is a Date/Time Data Type.

I go so far as to ensure that a suffix describes the Data Type in more detail. For example _Code is always 20 Characters, _Name is 50, _Description is a Memo Field. The actual lengths may vary from application to application but, within an application, they are applied consistently.

The advantages of doing this are mainly to do with coding. When I look at the Field Name, I instantly know exactly what data type it is and, if it is Text, how long it is. This saves me from having to constantly referring to the table design. This also ensures that fields with the same name in different tables are always of exactly the same Data Type and Size.