Microsoft Access Object Naming Conventions

By: Dan D'Urso
Published: 09th December 2002

When you create an Access database you should have a standard naming convention in mind. Conventions will vary by product, vendor, and most importantly perhaps, your company or organization. What follows is one that is common in the Microsoft Access community.

General Principles

Names should be concise but meaningful. Whenever practical avoid abbreviations. Isn't LastName better than lName or maybe sName(surname)? Believe me this can be very helpful. You won't have to remember later just what arcane abbreviation you chose. On the other hand don't get ridiculous. EmployeeID or EmployeeNo will do just fine. You don't need to write out EmployeeIdentificationNumber.

Do not include any spaces in the your names. Use a convention which capitalizes the separate words or separate them by an underscore. Like so:

last_name
LastName

Yes, Access will accept names with spaces by enclosing them in square brackets. For example: [Last Name]. But this can cause maintenance problems later. You don't want to have problems with a query because it can't recognize you meant the same thing by [Last Name] with two spaces and [Last Name] with one. This won't happen often but when it does it will be at the precise time you are under pressure to get a report out for management. Don't use spaces and you won't have this problem.

Prefixes

Access object names are typically prefixed with three characters that denote the type of object. This makes the type of object easy to spot in the expression builder, etc. What follows are the prefixes by object.
 

Tables

Table names should be meaningful to the application whenever possible. If for example you are setting up a database for a kennel you may want tables for dogs and breeders, not products and suppliers. An overdone example, perhaps, but I have actually seen this one!

Table names should be plural where it makes sense. I.e., employees, customers, products, etc. Many Access people prefix the name with tbl like so: tblEmployees.

Queries

Prefix query names with qry as shown below.

qryLaborDetail

If you like add an underscore. (This applies to other object names as well.)

qry_LaborDetail

Forms

Use frm. Example: frmWorkOrders. Subforms can be be prefixed with the four characters: sfrm. Some people use frmsub.

Reports

Use rpt. Example: rptLaborSummary. As with forms, subreports can be prefixed with srpt or rptsub.

Macros

Use mcr. Example: mcrCreateLaborHistory.

Database

Last, but not least give your database a meaningful name which relates to the application. Something like ShopControl or EmployeeBenefits. Not db1.

Read more from Dan D'Urso here