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.
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.
When naming fields your objectives are –
- 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.
- 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.
- 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.