DMW logo
contact@consultdmw.com

Conventions for Naming Objects in
Microsoft® Access Databases

A much simplified version of the widely regarded Leszynski naming convention for Access databases.

Last updated on 2020-12-28 by David Wallis.


Preface

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 any old names.

The convention I describe here suits me, as a developer. I suggest you consider it as you adopt one of your own. Other publications on the web may give you additional food for thought.

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:

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:

Relationships between tables for email addresses in databse

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.

Access database combo box 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:


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:

Access database form in design view

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.

Combo box on Access form in design view

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.

Label on Access form in design view

There are three text box controls on the form:

Text boxes in design view of Acess 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.

A tab control is comprised of the tab control itself acting as a container for page controls. Prefixes are tab and pg, as in tabActivity and pgActivityFun and pgActivityGames.The page names include the name of the tab control to avoid confusion should there ever be more than one tab control on the same form.

Option Group on Access form The option group control I’ve always known as frame. Hence the fra prefix, as in fraCurrency.

To form its name, each option button combines the ob prefix, the name of the option group, and its own identifier, as in obCurrencyGBP, obCurrencyUSD and obCurrencyOther.

Check Boxes on Access form

A check box control uses the cb prefix, as in cbSmall and cbExtraLarge.

If there is more than one grouping of check boxes on the same form, then I extend their names in order to distinguish to which group they belong. Hence cbAvailabilitySmall and cbAvailabilityExtraLarge.

List box object on Access formTaking 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.

Hedgehog play houseAn 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) —

“The beginning of wisdom is to call
things by their proper name.”

Confucius (551 to 479 BCE)