Conventions for Naming Objects in
Microsoft® Access Databases

Last updated on 2024-05-19.

Preface

The convention I describe in this article suits me personally. (It's a simplified version of the widely regarded Leszynski naming convention.)

I suggest you consider it as you adopt one of your own. There's plenty on the web to give you additional food for thought.

Consistently applying a convention for naming tables, queries, forms, reports, and the control objects in forms and reports, across all Access databases makes their creation and maintenance much easier than if you use names applied by default by Access.

Any convention is for the convenience of you, the developer. In a well-designed database, the user is not exposed to the names you adopt — the user interface shields the user from tech speak.

General Rules for Names

These suggestions apply to the drawing up of any name:

• Description Give an object a name that is descriptive of its purpose or function
• Brevity Keep a name as short as possible without detracting from its descriptiveness
• Mix Case Use a mix of upper and lower case characters in pursuit of deceptiveness
• Capitals Avoid names spelt entirely in capitals
• Lower Case Characters Avoid names spelt entirely in lower case characters
• Plurals I avoid them. If you're going to use them, then apply them consistently
• Spaces Don't use them in names. I know: sample databases, like NorthWind Traders from Microsoft, use spaces extensively in object names. But that's for making names that don't cause database novices WTF-is-that-all-about moments
• Underscore Characters Don't use them in any name
• Prefixes Use short prefixes as part of an object name to identify the type of object to which the name applies.

You'll find examples of the application of the above suggestions in each of the sections below.

Names for Tables

The prefix tbl identifies a table that contains primary data, as in tblPerson and tblPersonEmail tables in this example:

Prefixes are always lower case, making it easy to identify what's what in a table name.

I apply the prefix tlu to the names of tables storing lists, which do not change in the daily run of things. tlu distinguishes such tables from tbl-type ones, which carry variable data.

Entries in tluEmailPurpose provide the user with a list of the purposes to which a person's email address may be put. The list can then be the record source of any combo box we want to include in a form.

Occasionally, I find it necessary to include a routine that makes a table on the fly. Usually this table exist only for the lifetime of the routine producing it: when the routine completes, it deletes the table to avoid clutter. Such a table is a temporary table, and I include the ztbl prefix in its name, as in ztblDataLoading.

Names for Queries

The type of query is identified in the prefix to its name:

• Select query qs as in qsInvoiceDetail
• Delete query qd as in qdNullEntries
• Update query qud as in qudCurrentRates
• Append query qa as in qaExcelData
• Crosstabulaton query qct as in qctAnnualSalesByRegion
• Union query qun as in qunWorkDone.

Names for Fields in Tables

I've not experienced any disadvantage in not including a prefix in a field name. So, I simply make field names descriptive.

For the name of the primary key field in a table, I use the table's name (excluding the prefix) followed by ID. Hence, as illustrated in the table relationships diagram above, the field PersonID is the primary key field in the table tblPerson.

In many databases, a “notes” field appears in more than one table. I choose to identify such fields by including in their name the name of the table in which they appear. Hence PersonNote and PersonEmailNote.

Generally I don't pluralise field names. But on occasions it seems natural to do so, as if the nature of the information that the field is to store demands it, e.g. CurrentInitials in tblPerson.

Some writers recommend prefixes in field names to indicate the data type of the field, for example, int to indicate an integer data type, as in intProductCode. I've don't feel I've missed a trick by not adopting this style.

Names for Forms and Reports

The prefix for a form is frm; hence frmClass and frmStudent. These are the names of form objects listed in the database's Navigation Pane.

What hasn't worked well for me is the use of the subform prefix sfrm for a form object listed in the Navigation Pane. However, the prefix comes into use when a form is a subform control object on a main form, as explained in the section below.

As a way of distinguishing a form that exists only ever as a subform, I choose a name for the subform that relates it to the main form in which it sits. For example a form for invoices has a subform for invoice details. The form as a whole accommodates the one-to-many relationship between an invoice and its details. The chosen names, frmInvoice and frmInvoiceSubDetail, conveniently place the two together in the list of forms in the Navigation Pane.

The prefix for a report is rpt as in rptStudentAchievement.

Names for Control Objects on Forms

This form exhibits some of the common control objects used in form designs:

The form is a bound one; that is, it's displaying data from a query or table that's acting as the form's record source. The combo box has as its control source the CompanyID field drawn from the form's record source. I combine the combo-box prefix cbo with the name of the field to make the control's name: cboCompanyID.

A label control object includes the lbl prefix in its name. The Venue label is attached to the combo box. I use the name of the combo box's control source in the label's name to draw attention to this attachment: lblCompanyID.

There are three text box controls on the form:

The three are bound to fields in the form's record source. So, I combine the teb prefix with a field name to create a name for each text box, hence tebCourseDate, tebCourseName and tebCourseDescription. The attached labels follow the pattern: lblCourseDate, lblCourseName and lblCourseDescription.

The subform of attendees appears in the database's Navigation Pane listed as frmCourseAttendee. As a subform control on the courses form it's control name is sfrmCourseAttendee, i.e. the form name with a leading s tagged on to the pefix.

A command button is identified by the cmd prefix in its name, e.g. cmdOK. A toggle button adopts the tbtn prefix, e.g. tbtnOnOff.

Taking the example of the list box control, on the left, its row source is the table tblMaterial, which has the field MaterialID as its primary key field. The list box's control source is MaterialID. So applying the lb list box prefix and the box's control source we have the box's name lbMaterialID.

An image control combines the img prefix with a title that reflects the name of the file in which the image is saved, which in this example is imgHedgehogPlayHouse.

There are other objects that you might want to include in the design of your forms. Leszynski naming convention is one source on the web to refer to for additional names or if you want to apply names more widely that I do.

Names for Control Objects on Reports

Control objects on reports, like labels and text boxes, use the same prefixes as those on forms.

subreports bear the prefix srpt. For example, consider the two reports rptPerformance and rptPerformanceSummary as listed in the database's Navigation Pane. As a main report, rptPerformance contains rptPerformanceSummary as a subreport. In its role as a control object the subreport's name is srptPerformanceSummary

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —