Naming Database Objects

3 4

Using a consistent naming convention for database objects makes your database easier to work with, both when you are dealing with tables, queries, and other database objects in the interface and when you are writing VBA code.

Why Naming Conventions

Why use a naming convention? Basically, using a naming convention for database objects and controls makes your database self-documenting. Every time you see a drop-down list of database objects, each item’s tag (prefix) will tell you what kind of object it is. Some dialog boxes offering a choice of objects have separate pages for each type of database object, but others don’t. For example, when you select a record source for a form or report, all the tables and all the select queries in the database are listed in a single alphabetical list, so unless you have used a naming convention with distinctive tags for each database object, you won’t know whether you are selecting a table or query. Figure 4-1 shows the drop-down list for selecting a record source for a form in the sample Northwind database, which doesn’t use a naming convention for database objects.

figure 4-1. without a naming convention, there is no way to distinguish between tables and queries when selecting a data source for a form or report.

Figure 4-1. Without a naming convention, there is no way to distinguish between tables and queries when selecting a data source for a form or report.

Likewise, when you see a reference to an object, a control, a variable, or a field in VBA code, you’ll know what kind of element it is—essential information for understanding what you can do with it, so you won’t try to assign a currency value to a date variable, or use the SetFocus method on a field, for example.

Naming Restrictions

With Access, you can name database objects (including fields and controls) anything you want. Well, almost anything—there are a few absolute restrictions, as follows:

  • Names can’t be longer than 64 characters.
  • Names can’t include a period (.), an exclamation point (!), a grave accent (`), or square brackets ([]).
  • Names can’t start with a space.
  • Names can’t include ASCII controls characters (ASCII characters 0–31).
  • In a project, names can’t include a double quotation mark (").

It’s good to be able to give objects long, descriptive names. (This feature was especially welcome in Access 1.0, when many users were coming to Access from MS-DOS database applications, which limited object names to 8 or 10 characters.) But there are some problems with using spaces or punctuation marks in object names, even though Access allows them. If you give objects, controls, or fields names that contain spaces, Access won’t recognize these names as object, field, or control names in expressions, and so you’ll have to type brackets around them. (Access will automatically bracket single-word names in VBA code and query expressions, in most cases.) If you use punctuation marks in field names, and you export a table to another application that can’t accept specific punctuation characters that Access permits, you will have problems during the export, or afterwards.

You might also want to add the following naming restrictions, to prevent possible problems and make database objects easier to work with:

  • Don’t use spaces in names. Instead, capitalize the first letter of each included word (LastName), or use the underscore character (Last_Name).
  • Don’t use any punctuation marks in names, other than the underscore. Most punctuation marks won’t cause problems in Access, but if you need to export data to another application, you might have problems because the target application can’t handle dashes in field names, for example.
  • Avoid naming fields with words that are keywords, property or method names, or built-in functions (such as Name, Date, or Count), as they can cause errors in VBA code.
  • Use a naming convention to help you recognize and refer to different object types consistently.

A Simple Naming Convention

Chapter 20, "Customizing Your Database Using VBA Code," goes into detail about naming conventions. You may not want to apply a naming convention throughout a database, but there are great benefits from using even a simple naming convention that just prefixes database object names with a three-letter tag that indicates the object type. Table 4-1 shows a basic list of tags for the six main types of database objects.

Table 4-1. Common tags for database objects

Object Tag

Form

frm

Macro

mcr

Module

mod

Query (any type)

qry

Report

rpt

Table

tbl

For more detailed information on conventions for naming database objects, see Chapter 20, "Customizing Your Database Using VBA Code."



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net