CREATING DATABASE OBJECT BASE NAMES
The building blocks of your Access application are its database objects. When creating base names for these objects, you should give careful consideration to the purpose of the object, the approaches used to name associated objects, and the following rules of thumb for naming database objects.
Rules for Base Names
Follow these rules when developing a base name for a new database object:
Spaces aren't allowed in any object name. Spaces create a multitude of problems with consistency, readability, and documentation. Where a space would help readability, use an underscore instead.
Object names begin with a letter and should include only letters, digits, and underscores. Using special characters in object names is disallowed to comply with the naming rules of VBA and Microsoft SQL Server. This allows your Basic variable names to include database object base names and allows your entire Access schema to be easily upsized to a more powerful SQL Server platform.
Note
The first two rules also apply to the other naming convention elements: prefixes, tags, qualifiers, and suffixes. These elements should never include spaces or special characters.
Object names use mixed upper- and lowercase to add readability to the name.
Note
Previously, some developers used all lowercase names to allow for upsizing to Microsoft SQL Server. Starting with version 6.0, that product is now installed case-insensitive and allows you to maintain upper- and lowercase in object names that are moved to the server from Access.
The only syntax element that can have multiple capital letters is the base name. A qualifier or suffix begins with a single capital letter unless it's an abbreviation, as in qsumSalesmanPerfBestUSA. If you need to clearly see the elements of a name (prefixes, tag, base name, qualifier, and suffix), LNC allows for (but doesn't require) underscores as separators, as in qsum_SalesmanPerf_Best_USA.
Object names are usually singular (Widget) rather than plural (Widgets). By implication, tables, queries, forms, and reports are plural because they usually work with more than one record. So why restate the obvious?
An object's base name should include the base names of any table objects it's built on, if practical.
You should abbreviate object base name elements wherever possible by using a standardized abbreviation table, such as Table D.17 in the section “Standardized Abbreviations” later in this appendix. You can extend LNC with your own standard abbreviations as well. You should create and use standardized terminology in your applications wherever possible; for examples, see the section “Standardized Terminology” later in this appendix.
Base Name Length Limits
LNC includes some constraints and suggestions for object name lengths. We target our table name length at 15 characters maximum, for the following two reasons:
Short names (15 characters or less) fully display within the default column width of the Access query design grid.
Query, form, and report names usually include the base name(s) of the primary table object(s) they relate to, and are unusably long if the table base names are long.
Beyond the 15-character target, we absolutely limit our table name lengths to 30 characters, which maintains compatibility with the table name length limit in SQL Server. For other objects, we target a 30-character limit as well because Access shows no more than the first 30 characters of object names in the default width of any of its lists or property grids.
Compound Base Names
The name of an object that's driven by a table must include the base name of the table. Thus, for the tblCust table, the primary query would be qryCust, the primary form frmCust, and so forth. Queries, forms, and reports that are sourced from multiple tables should reflect the base names of all the tables, if practical. If not, you must decide which tables are the primary tables and list as many as possible in the name. Generally, in a multitable query, form, or report, the most important tables aren't necessarily the first and second, but more often the first and last. So, a query joining tblCust to tblAddr to tblPhone to get the phone numbers for customers would be named qryCustAddrPhone if the address information is included in the query result, or simply qryCustPhone, if the address information is used to join to the phone numbers and isn't displayed.
Bound control base names on forms and reports are always equivalent to the base name of the bound field (the control source). For example, a text box tied to the LastName field is named txtLastName.
Field Base Names
As a part of standardizing terminology, we adhere to the concept of an integrated data dictionary. This principle dictates that any fields in the data structure with the same name must have the same properties and data purpose. For example, if the LastName field in tblCust is of type Text 30 and holds the customer's last name, any other field named LastName in the same application must have the same type, length, properties, and purpose. If your application needs last name fields for customers and dealers, this rule dictates that you name them differently (such as CustLastName and DlrLastName).
Because I prefer my data dictionaries to be platform-neutral, applying the integrated data dictionary principle also means that table fields don't get leading prefixes or tags. That way, a field doesn't have to be renamed if data is upsized, merged, or ported to a platform with different data types. A table is still called a table in SQL Server, so moving tblCust there from Access requires no table rename. However, if tblCust had a field lngCustID defined as a Long Integer in Access, moving the database to SQL Server requires a field rename to intCustID because SQL Server uses the data type name Integer to mean the same as Access's Long Integer. Because renaming fields affects all dependent objects and code, it should be avoided at all costs, so I would call the field simply CustID from the start.
If field tags are part of your development style and you must use them regardless of my rationale, LNC provides the tags listed in Table D.3. As much as is practical, LNC maps similar Jet and SQL Server data types to the same tag so that that data structure can be upsized with a minimum of object name changes.
Note
The field type tags in Table D.3 are specifically designed to maintain the same field type tag in a pre- and post-upsized database (refer to the example of the mapping from Jet Long Integer to SQL Server Integer in the prior paragraph). If you are using an Access project against SQL Server exclusively, and you want to use field tags, you might want to use a more appropriate field type tag for SQL Server as opposed to using the tags in Table D.3 that map from Jet to SQL Server. For example, you would use int rather than lng for your SQL Server Int fields.
Unlike tags, qualifiers and suffixes are acceptable in field names because they describe the object's data purpose and not its type, and the purpose doesn't change between platforms.
Ordering Base Name Elements
Object base name elements should be ordered from left to right with respect to their importance, readability, and desired sort order. In the example from the preceding section, CustLastName is a better name than LastNameCust because the group name portion (Cust) carries greater weight in an object's name than the specific item name (LastName or PhoneNum). If you think of Cust as the name of a collection of customer-related items, this rule becomes clear—what you're really saying is that CustLastName is analogous to Cust(LastName) or Cust.LastName in Collection.Object terminology.
Some of you will naturally carry this example to its extreme and say that the Customers collection really has a Names collection with multiple elements, including Last, thus the representation of that idea as Cust.Name(Last) would lead to the field name CustNameLast instead. Such a construction model still fits within the rules of LNC, and I won't debate you against using it. In practice, however, such names often become fairly unreadable, even if they're accurate.