One Access application consists many objects. While developing an application, you may see and use each object's name dozens or hundreds of times. To help you manage and reference so many different objects, consider developing standard rules for naming them. Any object name you see and use over and over should be both informative and familiar (as some say, "easy on the eyes").
In this chapter, you'll explore and discover answers to the following questions:
In the original release of Access 1.0, many lists displayed tables and columns sorted together, for example, the Add Table dialog box in query design view. In such lists, it was difficult or impossible to differentiate a table from a query and knowing the object type was an important part of creating accurate queries. This challenge led many people, including us, to look for a way to clarify the two object types. Some people would put a lowercase q at the beginning of query object names so they stood out from tables. Others would use the q convention and also put a lowercase t in front of table names. Because we were already using a three-letter control naming convention from our form design work in Access and Visual Basic, we decided to apply that convention to database object names as well and began using qry to prefix query names and tbl for tables. This solved the problem of identifying object types within a single mixed list. Starting with Access 2, many dialog boxes that list tables and queries together included option buttons at the bottom of the dialog box to filter the list to show only one of the object types at a time. Nevertheless, in Access versions 2 through 97 the queries and tables are still listed together in the object selection screen of the Form and Report Wizards and in other places, so the need for a naming convention for these objects has not diminished.
While most serious developers live and die by their programming conventions, some are casual about issues like consistent object naming. To find out if the issue of naming conventions is important to you, take the following test:
In the Database window in Figure 5.1, can you identify
These form names do not provide adequate information.
If you were frustrated that the object names in Figure 5.1 were not very descriptive, you will get value from this chapter. For me, an application filled with names like those above is as difficult to navigate through as a messy garage!
To continue illustrating the value of naming conventions, next consider the Database window in Figure 5.2. The objects are the same, but the names follow a standardized convention. Even if you don't know the specific naming conventions used, I'll bet you can infer from the object names a lot about each object's important properties and identify the items on the list above.
These form names follow specific naming conventions.
The specific conventions used in Figure 5.2 are described in the next chapter, but you probably determined quite easily which forms were standard forms, which menu was the switchboard menu, which conventions were for lookup tables, and which form was actually a subform.
Notice that two types of naming conventions were applied in the example. The first, and most obvious, convention is the standardization of the object base names. C, Cust, and Customer, all meaning the same thing, were standardized to Cust. The Cust portion of the names was moved to the beginning of each base name so the customer objects would each sort with their peers. Also, the random use of singular and plural was stopped, and the names were made more descriptive.
The second, and equally important, convention is the addition of leading characters denoting the type of the forms. Many programming naming conventions have in common the standardization of the base object name and the addition of descriptive information on one or both ends of the base name.
I use the terms "naming conventions" and "style" interchangeably throughout this chapter. Naming conventions and other key development foundation items are each a part of your overall development style.
The motivation for creating naming conventions to work with your development tools usually derives from specific problems or situations you encounter. The previous example from my experience with Access (differentiating table and query names) is probably familiar to you as well.
There are other and more compelling reasons for developing and using naming conventions in an application. A description of each of the most important reasons follows.
When you look at an application that someone else has written or revisit your own after some period of time, the overall structure should make sense to you almost immediately. If not, you will waste time researching and reviewing it or chasing down documentation-if it even exists. While good naming conventions cannot help you to immediately infer the complex workings of the SQL in a query or help you read an intricate code module if you don't speak Basic, they give you at least an immediate treetop view of an application's architecture.
Suppose I'm visiting a client and they need me to give them an opinion on the viability of an accounting application they've written. In a perfect world, they would sit the application's developer down with me and take me through the facets of the solution step by step. In the real world, however, they will place me alone in front of a computer and give me only a few hours to understand their situation and make a recommendation because whatever objective they have was probably due yesterday.
Thanks to the enhanced Database window introduced in Access 97, that displays object descriptions entered by the developer, I am in a better position to understand the objects than I was with previous versions of Access. But screen real-estate is limited and descriptions are usually terse, so if this client has followed good naming conventions, I can also learn much about the application from its object names.
If the hypothetical client used my naming conventions, the Database window for tables might look something like the one in Figure 5.3 and from it I could divine the following:
The table names in this Database window are self-documenting.
When multiple developers are working on the same project, having a common methodology helps to pull them together as a team. With a stated set of standards and objectives, such as the following, individuals can work together more effectively:
When creating application development standards, including naming conventions, the expected benefit for team development can be summarized this way: Any member of the team should be able to quickly navigate and comprehend the application's object and program structure as if it were his or her own.
The code shown in Listing 5.1 provides an illustration of these points. Reading this code for the first time, you should easily see what the author intends for the code to do (to sum up data items from a transaction table). Not only are the base names descriptive, but the prefixes in front of the variable names also enable the reader always to know the context of a variable when seeing it anywhere in the same code routine.
Listing 5.1 AES_NAMG.MDB Dim Statements Using Specific Conventions
Public Function CustOrdrSum(rlngCustID As Long) As Currency
Dim curOrdrSum As Currency
Dim dbs As Database
Dim rstOrdrSum As Recordset
Dim varValidTest As Variant
Set dbs = CurrentDb()
Set rstOrdrSum = __
dbs.OpenRecordset("SELECT * FROM tblCustOrdr WHERE CustID = " _
& rlngCustID, dbOpenDynaset)
Do While Not rstOrdrSum.EOF
curOrdrSum = curOrdrSum + rstOrdrSum!Amount
CustOrdrSum = curOrdrSum
Users new to naming conventions and similar standards often complain that the conventions require extra time to implement and slow down the pace of development. While it is true that any new system involves an investment of time to learn, good standards will always save time, not cost time, in the long run. Also, applying consistent standards eventually becomes second-nature and requires minimal or no extra development effort.
Consider the Object Browser in Access 97. When you create a new form and view it in the Object Browser dialog box, you'll see exactly 154 built-in methods and properties listed for the form. The list is quite crowded before you've written one line of code. After you have written several code procedures in the form module, using the Object Browser to find those procedures among the several hundred total method and property items listed can be very time consuming. Figure 5.4 shows two code procedures (Validate and WidgetCost) aligned alphabetically with other items in the Object Browser.
These two functions sort in the Object Browser by their names.
Now let's apply a simple naming convention to the two procedures in Figure 5.4. Because these are form module procedures (often called "code behind forms"), we will prefix all the form's functions (and subs) with cbf. Placing these characters before the name sorts all form procedures with this naming convention together in the Object Browser. Figure 5.5 shows how the new sort order makes it much easier to go quickly to a specific routine or to find a routine when you don't know its precise name because all the form's procedures will sort together.
These two functions sort in the Object Browser by their type.
As a further example, consider what happens when you rename a table (or field, form, or any other object) in an application, for example changing the table name CustOrdr to CustOrder. You will have to globally find and replace every reference to the original name CustOrdr in all of your code. When you do so, the name may occur in other contexts than as a table name, making for a slow replacement process as you read through each match and check the context. For example, in the Listing 5.2, you may only want to change one of the four references to CustOrdr-the one that is a table reference-during your bulk replace operation.
Listing 5.2 AES_NAMG.MDB-Basic Function with Multiple Occurrences
Public Function CustOrdrArchive() As Integer
Dim dbs As Database
Dim CustOrdr As Recordset
Set CustOrdr = dbs.OpenRecordset("CustOrdr", dbOpenDynaset)
If, however, a unique naming convention was applied so that the table name was originally tblCustOrdr, the code in Listing 5.2 would have one reference to the target string (in the OpenRecordset method), not four. Your find and replace operation would be more precise and more rapid.
The final benefit in the list of naming convention justifications is the ability to extend your development environment and techniques by leveraging your naming conventions. Good object naming habits should enhance your ability to document an application, create tools and utilities, create reusable code libraries, and otherwise increase your productivity.
For example, with Access, you can create powerful add-ins and other tools using Visual Basic for Applications code. My company tapped into this power and developed our own set of documentation tools to analyze databases and create documentation files. The documentation routines take quite some time to run on large databases and produce large output files.
Because we use naming conventions, we can selectively disable documenting entire groups of objects by their type, thus saving time and disk space. Specifically, we might tell the documentation routines only to document entry/edit forms and their subforms (our objects with prefixes of frm and fsub), and to skip all switchboard menus (fmnu), dialog forms (fdlg), and all lookup table forms (flkp).
As another example, assume you use a common set of lookup tables and their forms in most of your applications, and you give these objects unique character prefixes. You could write a simple wizard to import specific objects from a master database. Your wizard could look to a template database and import all the lookup-type objects from there. If naming conventions are used, lookup objects are easily identified because all lookup forms in your template database might begin with flkp and all lookup tables with tlkp. It's not hard for your wizard to grab such lookup tables and forms on demand based on their unique prefix characters.
Even if you don't create your own tools, naming conventions facilitate better use of the tools in Access itself. For example, in the introductory section of this chapter, I demonstrated standardizing the abbreviation for Customer to Cust throughout a particular application. With this kind of standardization in place, all customer tables, queries, forms, reports, and macros in a system should have the string Cust in their base name somewhere. (In fact, in my world, even VBA variables that worked with the customer-related objects would have Cust somewhere in their name.)
Therefore, if you were looking for a particular set of program routines in your application, you could use the Access search engine to search all modules for the string Cust. You would quite successfully find all code routines for customer objects if you used such a convention consistently. Applying naming conventions to your daily work this way can be quite a time saver.
I summarized above the objectives I think of as most important when developing a naming convention. Before choosing or developing a style of your own, first list your own objectives if they are different from mine. Next, decide what considerations will affect the development of your conventions.
To create a style, keep the following four considerations in mind:
While my efforts over the last several years to help standardize VBA naming conventions have met with mostly favorable reception, there have been a few detractors as well. And, much to my amusement, a large portion of the people who tirade against naming conventions include some variation of this same quote in their messages to me, year after year: "Consistency is the hobgoblin of small minds." I wonder if people who use this quote for their purposes truly believe what it says. Do they really conclude that all people who choose to drive consistently on the right side of the road and stop at red lights have small minds? Do they think in their hearts that things like consistently on-time airline arrivals and departures are a sign of low intellect among airline pilots and FAA personnel? I could go on, but obviously from my perspective, consistency is in no way an evil thing. Using naming conventions faithfully does not imply that you are small-minded and a shallow thinker. Quite the opposite is true-it shows that you are smart enough to create repeatable systems to automate your mundane tasks to free up your rather large mind to float unhindered toward higher purposes! As a footnote, I find it equally amusing that not one person who has used this quote against naming conventions has actually used the exact quote correctly, that reads, "A foolish consistency is the hobgoblin of little minds" (Ralph Waldo Emerson). Notice that Emerson wasn't actually against consistency, only the foolish use of it. But, of course, people who twist this quote for their own purposes can't be expected to quote him consistently, right?
In my dBASE and R:BASE days, there were few published styles and little debate on the subject, so we just used a style that worked best for us and kept it to ourselves. The Access and VBA worlds present a different situation. Since the publication of our original Access naming conventions in 1992, we have received hundreds of e-mail and telephone suggestions and have reviewed more than a dozen other Access naming conventions, some derived from ours and others completely different. Developers, and even casual users, seem very interested in the subject and have widely differing opinions.
With a foundation laid of objectives and considerations, let's delve into the specific mechanics of naming conventions. You will want to consider each of the topics in the remainder of this chapter as you develop your style.
If you used no naming conventions at all, each object would still have a name of some sort. For tables, queries, and other database objects, these names are usually descriptive, such as Salespeople or FriendsOfMine. For program variables, programmers are usually more economical: the classic Dim I As Integer statement for loop variables comes to mind. You could call this starting point (Salespeople, FriendsOfMine, or I) the initial name, primary name, base name, or perhaps root name. For our purposes here, I use the term base name to describe the starting point when you name a particular object.
A strategy for selecting, abbreviating, capitalizing, and organizing object base names is, in and of itself, a naming convention. Therefore, if you do nothing more than create a consistent approach to base names, you have authored a development style for yourself. What topics would you include in a convention for base names? Let's examine the needs of specific Access objects.
Whether or not to use capitalization and punctuation schemes in your object names is discussed in the "Capitalizing and Punctuating" section that follows the discussions of individual object naming strategies. Normally, styles for capitalization and punctuation are applied uniformly within a convention rather than varying by object type.
When naming tables, your standard should specify a desired target length for names. Some developers prefer things very terse and use the shortest name they can read. (Although that doesn't ensure that others can read it!) One Access naming convention I reviewed suggested limiting table names to eight characters to be compatible with dBASE and FoxPro formats! I find such short names to be totally non-intuitive to read, and would not recommend such a restrictive approach unless you routinely move your Access data to a platform with shorter name restrictions.
I spent a decade working on products with eight-character names and am happy to be free of that bondage. While most users can infer that CustInfo is the customer information table, far fewer will recognize that InStTxSl is the table for "in-state taxable sales," because it could just as easily be the "installed steel in Texas's silos."
At the other end of the spectrum, some developers prefer longer names that are fully descriptive. Long names may even refer to the source or range of the data. Examples include MainEuropeanCustomerData, north region sales summary from db2, and WidgetProductionStatisticsFirstQuarter1997. While these names are valid in Access, they become rather unwieldy when you use them in your application, for example in the query grid or in SQL statements and other strings in VBA code.
For table names, as with most things in life, moderation is probably the best approach. If you create a set of standard abbreviations for your development (see "Abbreviating Object Names" later in this chapter) and remove extraneous words, you can create table names that are not overly long and yet are fully descriptive. I prefer to keep table names to 15 characters or fewer through the use of standard abbreviations. At most, I use a 30 character absolute limit for my table names so they can upsize eventually to SQL Server without exceeding that product's length limitation.
Another justification for modest table name lengths is that table names often become part of the base name for other objects that depend on them, thus long table names breed even longer names for queries, forms, and reports. For example, a table Customer would probably have several queries based on it, like CustomerSalesSummary, CustomerByRegion, and so forth. The longer the table name, the longer the names derived from it.
The final consideration affecting table name length is a very pragmatic one. The Access user interface provides limited real estate in property pages, combo boxes, and other objects that list table names. Table names wider than the display space that Access provides are difficult to view and work with in the user interface. Table 5.1 shows the default width in average characters displayed by the Access 97 user interface in various places. As you can see from the different widths listed, table names longer than 30 characters are routinely truncated, and even those longer than 15 characters will sometimes be trimmed when displayed.
Table 5.1 Default Average Display Width of Table Names
|User Interface Item||Default Width (Characters)|
|Form Record Source Property||30|
|Form/Report Wizard Source Object||25|
|Query Design Grid Field List||15|
|Query Design Grid Table Combo||17|
|Macro Action Argument||32|
In addition to the areas listed in Table 5.1, table names appear in your query SQL statements and in your VBA code as Recordset sources and SQL parameters. Table names are the most widespread object names in a typical application. Something that gets that much usage deserves a few moments of your quality thought when you give it a name.
Finally, I prefer to keep my table names singular (Customer) rather than plural (Customers). By implication, a table is always plural because it contains more than one record, so why waste one or more characters (to form a plural) everywhere you use its name? Whether you prefer singular or plural names, remember to pick one approach and apply it uniformly. I frequently see other developers mix and match singular and plural object names, and some confusion can arise when users try to decide whether they should enter more than one record in tables with singular names or only in tables with plural names.
In the discussion of table name length above, I noted three sample table names that were long enough to be problematic for us. I'll now restate those names, along with a suggestion of how I would rename them based on the factors we've just considered, as follows:
An object naming convention rarely stops with base names, but if you have no need for more elaborate conventions, simply apply the forethought described in this section to table names to develop standards for good base names and make your application development and use more pleasant.
The considerations discussed in the preceding "Table Base Names" section apply to any discussion of field names as well, including the following:
Because a table holds many different types of information and a field holds only one, it is easier to shorten field names than table names. Whatever target you select for table name length, your field name standard length should be less than or equal to, but not longer than, the standard for table names. Like table names, field names appear in the user interface in property dialog boxes, the query grid, the macro action grid, and so forth, and each of these areas has display-width limitations for object names. You will find that sticking with field names around 20 characters long removes most annoying truncations in the Access 97 user interface.
One common discussion that arises during the development of field naming conventions involves the order of compound elements in the name. Some compound name ordering questions are easily resolved-LastName is generally thought to be a more readable field name than NameLast. Other names are equally adequate in any order, such as date field names like ClosedDate versus DateClosed. And because field names usually appear in the Access user interface in natural order rather than sorted, the sort order of field names does not have a large impact on your development.
If you opt for the style ApvdDate and ClosedDate, you are placing more weight on the content part of the base name rather than the data type. If you opt instead for DateApvd and DateClosed, you are placing more emphasis on the type of field. I view this latter style as the more dangerous of the two because when you carry it out to its logical conclusion, it may lead to confusing names. For example, CommentMgr is a name for the Manager Comment field that places emphasis on the type, and this name is less accurate than MgrComment. A type-based construction becomes essentially unworkable beyond date and time fields.
As noted in the "Table Base Names" section earlier in this chapter, many query names are built around the primary table or tables in the query. Therefore, query names are longer on average than table names. One reason I had suggested 15 character table names is so that the length of query names built around short table names will also be reasonable.
Refer to Table 5.1 where I listed maximum display sizes for table names and note that the Access 97 interface provides 30 characters or fewer of table name display space by default. In every case, the user interface areas in the table listing can also display query names, so to try to keep query names to 30 characters, building them around table base names of 10 to 15 characters is a good rule of thumb.
Having decided on a target length for query names, applying the standard can become a challenge. It is harder to be terse with query names than table names because queries perform actions on data or change the presentation of data. A good query name must express the actions it performs on the data. To solve this problem without being verbose, I prefer to use prefix and suffix characters placed around the query base name to express the query's actions succinctly, rather than including action information in the base name.
For example, a query to delete all dormant sales prospects could have a base name like Cold Prospects Delete or perhaps DeleteDormantSalesProspects. For my tastes, these are both too wordy and they marry the action too closely to the base name. My preferred approach would be to keep the base name simple, SalesProsCold, and describe the action using a prefix or suffix. Using this approach, the base name describes a group of records independent of the action so it can be reused in various related objects. Adding an action prefix or suffix to this query would allow for multiple "flavors" of the same base name: qselSalesProsCold, qdelSalesProsCold, and qupdSalesProsCold.
Finally, I prefer to keep my query names singular (Customer) rather than plural (Customers) because a query is usually plural by its nature. Select a pluralization scheme for your queries and apply it uniformly.
Because form and report names are less widespread throughout both your application and the Access user interface (they appear mainly in OpenForm/OpenReport actions and in the Database window), you have more leeway on the length of their names than with other object names. Access allows object names up to 64 characters long, but few people find that such long names are usable in practice.
You may want to establish a name length standard for form and report names. Like queries, forms and reports are based most often on a source table or tables and the object name derived from the source table names. Therefore, I would suggest using the same name length guidelines you establish for query names as your form and report name guidelines. More important than discussing length is establishing rules for constructing the names of these objects.
There can be many different flavors of forms. Forms can be dialog boxes, switchboard menus, data entry screens, record edit screens, form/subform combinations, record selection lists, floating (pop-up) text or utilities, and more. Similarly, reports can provide detail, summaries, computations (totals, averages, and the like), access to legacy (mainframe) data, and so forth. Therefore, how to convey a form's or report's purpose to users and developers without being wordy should be the primary focus of your conventions for these objects.
Consider a form to edit customer orders. It is possible to create an Access form with customers on the main form, orders in the subform, and order details in the subform's subform. The driving table for the main form is the customer table, but the primary purpose of the form is to get to the order details. So, is the form's base name Customer, CustomerEdit, CustomerOrder, Customer Order Detail Edit, or one of a dozen other possibilities? This is an example of the type of problems you should think through clearly as you create your naming conventions. Good conventions should help you and your co-developers approach such problems with ease.
How would I solve this specific dilemma? Our conventions require that the base name of the primary table be a part of the form or report name. It could be argued that the primary table here is the one that provides the most detail to the form (CustOrdrDet on the subform), not the one driving the main form (Cust). Therefore, I would name the form frmCustOrdrDet; if there were two varieties of the form, I'd use frmCustOrdrDetAdd and frmCustOrdrDetEdit.
Similar questions arise for reports as for forms and can be addressed with the same approach. The standards you create for form base names should be applied to reports in identical fashion. Report names tend to be even longer than query and form names because you will often have several flavors of a particular report: a detail version, a summary version, and versions that group, sort, and total differently to meet specific needs. Your standard should address how to differentiate these versions with a minimum number of characters; a good abbreviation system will be especially useful here.
Subform and subreport naming conventions sometimes specify that the base name of the parent form is reflected in the base name of the subform or subreport. This strategy becomes challenging, however, if you create subforms/subreports that reside in more than one form/report. Your naming convention for subforms and subreports should address how to handle this contingency, most commonly by giving the subform/subreport a base name that describes the data that it's bound to, not the parent object(s).
When you create a bound control on an Access form or report, the control automatically inherits the field name of the bound ControlSource as its name. While this approach is adequate for most purposes and relieves of any responsibility from you for assigning a base name that it may be lacking in two areas.
The first concern is a practical one. You cannot evaluate some expressions on forms and reports when the name of the control is the same as the name of a table field in the object's recordset. For example, if the following expression is used as the ControlSource for a report control named Qty in a report whose RecordSource also contains a Qty field, the report will display #Error instead of the proper calculation:
The situation created here is called a circular reference, in that Access does not know whether to evaluate [Qty] in the expression as the table field with that name or the control with that name. Giving the report control a name different from the bound field name, such as txtQty, will solve the problem.
The second concern is more esoteric, but in the same vein. Philosophically, should the name of a control, that is an object, be the same as that of a field, which is also an object but of a different class? I would argue no.
The objective of naming bound controls differently from their related fields is easily implemented simply by applying a naming convention to the control name. Thus, the control's base name can be exactly the same as the bound field's name with the addition of prefix characters to prevent circular references.
For unbound controls, determining the approach for base names is not quite so obvious. An unbound control may accept user inputs, display the result of an expression, or provide program code with a temporary workspace for values. Thus, my recommendation would be to apply the same logic stated previously for naming table fields. For example, if your naming convention would produce a field name of LastName rather than NameLast, you would use the same rules to produce an unbound control base name FullName as opposed to NameFull.
Macro names are even less widespread in your application than form and report names. They appear primarily in event properties for forms and reports where Access 97 by default displays about 30 characters of space. You may want to consider this character count as a reasonable limit for macro names.
As part of your development strategy, you may have already decided how related actions will be grouped into macros. One common approach is to place all the macros for a specific form or report into one macro group. If you follow this convention, the macro base name would include the base name of the associated form or report. If you create individual macros for each form or report event, consider showing the event name or an abbreviation in the macro name, for example mfrmCustCurrent and mfrmCust_AftUpd.
Because creating distinct macros for each form or report control event could lead to hundreds of macros, you will most likely group such macros together by control or even by form. If you are grouping by control, consider including both the form and control name in the macro name for clarity: mfrmCustTxtCustName or perhaps mfrmCust_txtCustName.
Table 5.2 shows examples of abbreviated form and report event names that keep the macro names as short as possible.
Table 5.2 Examples of Abbreviations for Event Names
Notice how the number of characters used in the abbreviations is not fixed. Rather, my goal in this example is to express the event name as clearly as possible in four to six characters, targeting the least number of characters to maintain readability. For an expanded discussion of abbreviations, see the following section, "Abbreviating Object Names."
The names of generic macros (not attached to one object) are subject to much the same discussion as are modules, so refer to the discussion of module base names in the following section.
Module names are usually meaningless to users (who never view your code), so they often follow the convention that is most convenient for the application developers. Your coding standards may already dictate how related procedures will be grouped into modules. In such a case, the module name will or should reflect the type of procedures it contains. You or fellow developers should be able to quickly determine the most logical module to look in to find a specific code routine. To accomplish this, your module names will generally be longer and more specific than names of other objects, for example basAPIRoutines_FromMSDN or basAPIRoutines_Registry.
Unlike the organization of macros discussed above, you usually will not place procedures for one specific form or report into a module; such code would be stored instead with the form or report. If some need arose to group procedures for a specific object together, however, these guidelines for including the object's base name in the module name would apply.
Naming code procedures (functions and subs) is often a very whimsical task. Some applications involve hundreds of procedures and thousands of lines of code, yet the average developer gives no more than a second or two of thought to naming a new procedure. Procedure names are often repeated (called) in many other procedures, so they become more unwieldy as they grow longer. On the flip side, procedure names usually cannot be descriptive in a mere 10 or 15 characters. Your naming convention should prescribe techniques to find a balance point between names that are too long and too short. Using abbreviations can help keep your procedure names short.
Some developers create a naming convention of leading or trailing characters to delineate functions from subs. You should consider whether this technique is useful for you or not. In practical application, subs sometimes become functions later (see Chapter 11, "Expert Approaches to VBA"). Therefore, I don't identify the type of a procedure in the name (such as subLogin), but it would be a perfectly legitimate thing to do if it suited your needs.
It may also be useful for you to formalize the construction of your procedure names. Consider these three function names, which all could describe the same function:
There is nothing inherently wrong with any of these names. The function name DatabaseName() is too broad for my liking, however, because I can't tell by looking if it retrieves or sets a database name. The second option, GetDatabaseName(), is better because the leading action verb tells me more specifically what the function does.
If I consistently apply the philosophies in this chapter, however, I should arrive at a preference for the name DbsNameGet() for two reasons. First, the name begins by describing the object then the action. Because I prefer my object names to achieve some end result with respect to sorting, this convention causes similar routines to sort by object. DbsNameGet() and DbsNameSet() would sort together in procedure lists; the alternate construction GetDbsName() and SetDbsName() would cause them to sort farther apart by action. In general, I prefer first to place the object name then place the verb when naming procedures. You should decide for yourself whether you prefer this ordering logic or the reverse.
Second, the name includes a standard abbreviation to keep it short. In this case, the abbreviation used is the most readily available-our descriptor for database objects.
On the surface, it would seem nonsensical to ask the question: "Who doesn't use upper- and lowercase mixtures in their object names?" The answer may surprise you.
As an example, before version 6.0, SQL Server's default installation mode made it case sensitive with respect to object names, including table and field names. This fact led Access developers who intended to migrate the database schema to SQL Server later to prefer lowercase names for tables and fields. Otherwise, when the application was upsized to the server, mixed-case object names had to be changed to lowercase and supporting objects and code had to be modified to reflect the rename. With SQL Server 6.x, however, the default (and preferred) installation mode is case insensitive, making it easier to upsize Access databases to the product without any name changes.
Also, I have met many developers who are religious about economizing keystrokes. Such people often work with lowercase object names to save their pinkie fingers thousands of visits to the Shift key on the keyboard each day. Conversely, developers who cut their teeth in the years before personal computers can remember terminals that had no lowercase letters. They may work mostly in uppercase out of habit.
With these points considered, it is true that the majority of developers work with mixed case when naming objects. Each independent part of an object's name is treated as a proper name, with the initial letter capitalized. Thus, customer personal information goes into a table named CustomerPersonalInformation or perhaps tblCustInfoPers.
The following are two caveats to consider with this simple capitalization rule:
Simply because your base names use upper and lowercase does not automatically mean that your naming conventions should be mixed case. Some naming convention elements (leading items) are actually more effective in lowercase, as in the descriptor tbl in the previous paragraph. See the section "Applying Naming Conventions to Your Work" later in this chapter for a discussion on this point.
Also, mixing case may not automatically make object names readable. Some developers prefer to use additional punctuation to further improve readability. The first and most obvious option is to add spaces to names. Evaluate carefully whether or not this is a good option for your application and/or your overall style. Adding spaces to object names means that you will have to bracket the names (for example, [Customer Info]) when working with the objects in VBA code, macros, and expressions.
Spaces also remove the ability to easily upsize the application to a server platform because virtually none of them support embedded spaces in object names. Finally, spaces in names will make it more difficult for you to create tools or Data Access Object routines that involve any string parsing (separating component items) because you will have to look for both brackets and multiple spaces in object names when grabbing them from inside a string or string variable.
In addition to spaces, I am basically sour on any kind of punctuation in object names except for underscores. This ensures that I can move data from the Access application to other platforms without a rename. For example, SQL Server allows only letters, digits, $, #, and underscores in names. Visual FoxPro is even more restrictive, allowing only letters, digits, and underscores.
I also frown on using characters in one context that have a different meaning in another context; I would rather be more consistent even if Access doesn't require me to be. For example, the apostrophe is a comment marker in VBA code but a valid character in Access object names. If I see apostrophes in object names, my brain thinks that the part of the name after the apostrophe is a comment! You will have to decide just how restrictive to make your naming conventions in this respect.
When punctuating names by using underscores, consider whether they should be allowed at all locations in a name or only in certain contexts. Although a table named tbl_Customer_Info_Archive is certainly readable, I don't want to spend my life adding that many underscores to all object names as I type. In the discussion of qualifiers and suffixes later in this chapter, you will see that underscores may be of great value in specific contexts.
Earlier in the chapter you discovered that using standardized abbreviations can be useful in shortening object names without losing meaning. Typically, when the design phase of an application is completed and table and field names are being finalized, you should look for opportunities to apply your standard abbreviations or to create new abbreviations needed for the project. The tables in the "Standardized Terminology" section in Chapter 6, "Leszynski Naming Conventions for Access," show a list of some of the standardized abbreviations my company has created and used consistently over the years.
Abbreviations that we use ourselves tend to fall in the three- to five-character range, although some variances occur for clarity. Few words require more than five characters to convey the original word with no loss of readability or meaning. Also note how many characters you can eliminate in an object name when you use abbreviations-in the case of admin as a short form of administration, nine characters are saved.
When creating standard abbreviations, some developers establish restrictive rules for abbreviating. For example, I've seen conventions using a rule like this: "Remove all vowels past the first character until five characters remain. If the abbreviation has fewer than five characters, add back vowels from left to right until a five character length is achieved." Table 5.3 shows a few of our standard abbreviations and the revised abbreviation using the stated, stricter rule.
Table 5.3 Leszynski Company Abbreviations Contrasted with Strict, Five Character Consonant-centric Abbreviations
|Term||Our Abbreviation||Revised Abbreviation|
Witness the dramatic difference in abbreviations using the harsh rule. The revised abbreviations are, in my opinion, less obvious than our original versions in four of the six cases. Abbreviating beginning as bgnnn is particularly unusable. As opposed to a firm abbreviation rule, our approach to abbreviating is more pragmatic: using as few characters as possible, convey the full term using characters from the term or an acceptable shorthand. (The "acceptable shorthand" clause lets us use commonly accepted contractions like xsfr for transfer.)
This example serves to drive home an important point about abbreviations and, by inference, about naming conventions in general: Your rules for naming objects should be flexible enough to serve the ultimate objectives, not restrictive for their own sake.
At this point in the chapter, you've wallowed through the philosophy of object base names, how to shorten them, and how to standardize abbreviations. With a strategy for shorter and meaningful base names in place, the remaining piece of the puzzle is a strategy for ordering the components of base names.
In the field name discussion above, I showed the example of shortening the table name WidgetProductionStatisticsFirstQuarter1997 to WidgetStats97Q1. Why did I select this particular order for segments of the name? I did it with sorting in mind.
By design, the Access Database window and various combo box lists in Access sort object names alphabetically. When I name related objects (those with similarities in the base name), I want them to sort close together so that they appear consecutively in ordered lists. So, the first rule I apply is to start with the most important part of the name at the left. If every object dealing with widgets begins with Widget, the objects will sort together. Next would come the second most important part of the name, then the third, and so forth.
Notice in the example at hand that I changed the trailing FirstQuarter1997 portion of the example table name to 97Q1, a convention that ensures that similar object names will sort by year, then quarter, producing a list like this:
In the prior example, I abbreviated 1996 and 1997 in the object names as 96 and 97. For objects that have a short lifespan, such abbreviations are acceptable. However, if your objects or data will persist past the turn of the century, you should begin using full year designations now, otherwise sort orders will be affected in 2000. For example, "99" items will sort after "00" items, but "1999" items will properly sort before "2000" items (all other things being equal).
Figure 5.6 shows a well ordered Database window for which consideration has been given to making the query names sort in a logical sequence.
These query names sort in a logical sequence.
With conventions firmly in hand for object base names, you can proceed to creating the wrappers around the base name that provide extra information and capabilities. The most important, in my view, are tags (see Figure 5.7).
The form controls in this list are easily identified by their three-character tags.
In the 1980s, a Microsoft programmer named Charles Simonyi wrote a paper titled "Program Identifier Naming Conventions," which some people point to as the beginning of formalized C language naming conventions. Actually, Simonyi's article was narrowly confined to (non-object-oriented) C programming and concentrated on philosophical as well as practical issues. Nevertheless, it was one of the first times in print that the issue of variable names was analyzed broadly. The paper also popularized the terms "tag" (a short prefix) and "qualifier" (a scope limiter placed after the tag), that are still in common use today. (Although before the terms existed, programmers were still using the concept. For example, we used one-character variable tags in our dBASE and R:BASE work starting in 1984.)
For our purposes here, I'll use tag to mean a set of characters placed against an object base name to characterize it. In object-oriented programming terms, the tag is somewhat analogous to an identifier for the "class" (a definition of a unique object type). The biggest challenges for you as you define your naming style are to define
You can place tags before (as in qselCust or qdelCustClosed; see Figure 5.8) or after (as in Cust_QSel or CustClosed_QDel; see Figure 5.9) an object's base name. As examples throughout this chapter have shown, I have standardized tag placement to come before the object name for my style. To be fair, however, I will discuss both placements in the next section. Placing tags in front of object names is sometimes called "Hungarian Notation" in reference to Charles Simonyi's nationality.
These query names have leading tags and sort by type.
These query names have trailing tags and sort by base name.
Why use tags at all? In an environment like Access, where terms such as object, class, and property are becoming more and more important, a base name by itself does not convey enough information for many developers. It can be very convenient to encapsulate within an object's name information about the type of the object (that is, its class and/or primary properties).
For example, reading through VBA code that executes a saved query whose name has no type information, you would have to look at the Database window to determine the type of the query (indicated by its icon). A query name with a type tag on its base name, such as qdel for a delete query, is instantly recognizable in your code as to its type or class. This can provide programming benefits such as enhanced program readability and fewer coding errors.
In addition to these advantages for VBA programmers, the following are a few other benefits of tags:
As the examples above imply, you can have tags that are broad (qry for query) or detailed (qdel for delete query). You must weigh the advantage of the detailed versions-they are more informative-against the added cost of creating, learning, using, and maintaining a larger number of tags.
The placement of tags can become a hotly debated issue as you standardize: Are tags that precede a name better for us than those that follow the name? As the listings in the previous figures point out, tags placed on objects in sorted lists cause them to sort by type before name. But casual users are least interested in an object's type, so they will be the first to deride leading tags.
Some programmers also prefer to scan an object list and see the base name as the primary sort order. With such individuals around, you might opt for trailing tags.
To recap, programmers who are more interested in an object's properties than its name (as I am) will usually opt for the leading tag scheme, while coders who are focused on base names will place tags after that element.
If you have decided to use tags, you next need to determine which objects should be tagged. Consider each of the following Access objects as candidates for tags, and note the order of the list:
I have ordered this list based on my experience reviewing many different conventions and talking with other developers. The list sorts from the most frequently to the least frequently used object naming conventions. In other words, most developers who use tags use them in VBA program code (the first item in the list), while very few developers who use tags use them on table fields (the last item in the list). The following is a short summarization of each item on the list.
This VBA code demonstrates variable declarations that use type tags.
Tags on query names cause them to sort separately from tables in wizard lists.
Tags on control names cause their events to sort by the tags first.
These form tags provide a high degree of specific information about the type of form.
Refer to Appendix C, "Leszynski Naming Conventions for Microsoft Solution Developers," for insight into the difficulties provided when trying to standardize table tags in an application with multiple data platforms (Jet and SQL Server, in this case).
Leading table tags group these tables by the type of data they contain or their role in the database.
These macro tags clearly identify form macros, general macros, menu macros, and report macros.
Module tags usually mean more to the developer than to the user.
Procedure tags can indicate a procedure's return value.
Some developers use tags that are as short as a single character. While this certainly economizes on keystrokes, such tags are not very intuitive except in limited cases. For example, there are 11 intrinsic variable types in Access 97 VBA (not including the Data Access Objects, user-defined types, and specific object types), few enough that you could conceivably create a single-character tag for each.
Many developers like single-character tags because they use a minimum of keystrokes when added to an object's name. I feel that one-character tags are too obscure and limiting. (The limit is the 26 letters of the alphabet, which doesn't allow enough room for growth.) For an example of single character tags, see the Microsoft Visual FoxPro help file, which shows suggested single-character variable tags for FoxPro developers. In the FoxPro convention, the tag c is used for Character variables and the letter y denotes Currency types (see Table 5.4). An Access naming convention based on this model might use s for String variables and c for Currency. Most users would not find this notation intuitive at first glance, however.
Table 5.4 Single-Character Variable Tags Recommended by Microsoft in the Visual FoxPro Documentation
Like me, many developers find that single-character and even double-character prefixes are not intuitive enough to serve as tags and that three- and four-character tags are more useful. This tag length is the most prevalent across the majority of published Access styles, although those who use this style admit that it introduces quite a few extra keystrokes into development efforts. If you're not averse to even more keystrokes, your style could conceivably use more than four characters for tags.
Be aware that you cannot use reserved words as object names, so only the second syntax below is legal as a naming convention in Access: Dim int As Integer Dim intWork As Integer
Table 5.5 shows several different tags for selected Access objects, culled from various naming convention documents that Access developers have sent to me over the past two years. Note that none of the tags exceed five characters and that different developers often come up with very similar tags.
Table 5.5 Selected Tags from Various Access Naming Conventions
As I noted in the previous section, you can place tags before (as in qryStateCapitol) or after (as in StateCapitol_qry) the base name. Most developers place them at the front, but in some naming conventions, tags trail the name. Both the primary benefit and the primary liability of leading tags are the same: the sort order of objects is affected.
In my experience, the best approach to deciding on a placement strategy for yourself is to create two small, similar applications. Use the leading tag convention in one and the trailing tag convention in the other. Comparing the pluses and minuses of the two approaches after actually using them in a hands-on experiment is the best input for making your decision.
At this point, you have decided on a tagging strategy that includes length and placement. With such guidelines in place, you must create the tags themselves. The following are four rules of thumb that I suggest you apply to the process:
If you've gotten the impression that there is no single simple rule for creating tags, you're right. The process is unique to your needs and experience as an individual developer or development team.
By definition, a tag describes the type of the object. Some objects can have attributes (essentially properties) other than their type that give them additional capabilities. For example, a VBA variable in Access 97 can have a scope, Public or Private, in addition to its data type. Further, it may be helpful to you to denote static variables with a prefix in front of the tag, such as the following:
Static sintSubtotal As Integer
In your naming convention, you should consider whether it is important to add such identifiers to your tags to describe scope and similar attributes. I call an identifier that precedes a tag a "prefix," which you could essentially define as a clarification string placed on an object tag.
How you use and define prefixes depends on your needs. There are several places in Access where extra qualification of a tag can be useful, and prefixes are obviously helpful in such cases. Table 5.6 lists examples. There may be other areas where your own needs and development style lead you to create specific prefixes for specific needs.
Table 5.6 Areas in Access Where Tag Prefixes Are Useful
|Object Type||Prefix Use|
|Tables||To denote specific table characteristics, such as system tables, archive copies, work in progress, and so on|
|Queries||Same as for tables.|
|Forms||Same as for tables. Also, you could debate whether a subform is a form object with a prefix in front of the form tag (for example, sfrm) or a unique object type that requires only a distinct tag (like fsub). I lean toward the latter.|
|Reports||Same as for forms.|
|Macros||Same as for tables.|
|Modules||To designate specific attributes of the module. For example, you could create specific prefixes to denote modules containing only API calls, constant declarations, company library routines, and so forth.|
|Controls||To designate more specific information about generic controls. For example, an Unbound Object Frame in Access can contain various kinds of OLE objects. You could create prefixes to differentiate PaintBrush picture object frames (pfrb) from Excel worksheet object frames (xfrb).|
|Procedures||To designate procedure scope (Public versus Private versus Static). Also, when creating public library routines, some developers use a unique prefix (in our case, lci_ for Leszynski Company, Inc.) to clearly designate such routines.|
|Variables||To designate variable scope (Public versus Private versus Static) or the type of variable passed as a parameter (ByRef versus ByVal).|
Because a prefix often equates to an object property, any object that can have multiple properties can legitimately have multiple prefixes. Thus your conventions will need to prescribe ordering rules for using multiple prefixes. For example, your program code requires a public (a p prefix) string array (an a prefix) of parts.
The two options for prefix order are pastrPart or apstrPart; you will have to select one of these orders. Creating an integer variable to index this array is a bit more problematic, however, because it would require three prefixes: p for public, a for array, and i for index. Thus, you could create six combinations of prefix orders from these prefixes, including paiintPart, piaintPart, and aipintPart. Your convention should detail how to decide which prefix order is appropriate when such compounding occurs.
A "qualifier" is a naming extension that provides context to the specific use of an object. Unlike prefixes, which detail properties of the object (for example, that the variable has global scope), qualifiers describe how the object is being used in context. For example, assume you need three VBA variables to track movement through an array: the first item, the current item, and the last item. You could consider using the qualifiers First, Curr, and Last at the end of the object names to make the names unique and their purpose obvious while retaining the same base name:
Placing the qualifier after the base name like this allows the object base names (Part) to sort together. An alternate construction would place the qualifiers after the tags rather than the base name to drive the sort order differently:
If qualifiers in your style always came at the end of the object name, they would actually be a type of suffix. I find the term qualifier superior to suffix, however, because it is location-neutral-qualifiers may come before a base name, depending on your style. Further, you may want your naming convention to include both qualifiers and suffixes. This construction can get slightly complicated for its users, but the qualifier would still describe the use and the suffix would designate still other information about the object.
For example, your company creates separate queries on a per-state basis so that state sales managers can review their employees' performance daily. While this operation could be done with parameter queries, your company has specific reasons to make a different saved query for each state. Your naming convention could dictate that state abbreviations are legitimate suffixes so that your queries could look like the following list (note that the names in the list also include the qualifiers Best and Worst):
In the example above, I use underscores to offset the suffix from the qualifier. The reason is that I want the qualifiers Best and Worst to be easily recognized when the names are read because the difference between the meaning of these two qualifiers is substantial. Some developers also prefer to use underscores before all qualifiers and all suffixes to pull them further from the base name. You should consider whether or not this is a good strategy for your style.
Your naming convention may legitimately be able to exist in a vacuum. If you are an independent developer and expect to be working only in Access for many years, you could create a development style that was Access-centric. Most developers use a variety of tools, however, which introduces both a problem and an opportunity.
The problem is that developing multiple styles can be time-consuming, learning the styles and keeping them separated at development time can be challenging, and styles with overlapping elements can breed confusion. For example, what if the same tags in two different platforms have two different meanings for a perfectly good reason?
The opportunity presented is for you to create a style that transcends products and dictates how you deal with common objects wherever they may reside. This task becomes easier with each release of Microsoft Office as the individual products in the suite begin to have more features and objects in common.
For example, in addition to Access, you can create Check Box controls in the other Office applications as well as Visual Basic and Visual FoxPro. As you create naming conventions for each of these tools, you will be best served by standardizing your tag for CheckBox across all these products. Your developers will only have to remember one CheckBox tag for all of the VBA host applications.
As a contrary example, prior to Office 97 there was a Label control type in Access, Excel, Visual Basic, and Visual FoxPro, while PowerPoint called the same control a StaticText control and Word called it a Text control. When you create a naming convention in an environment such as this and your convention must account for the disparate control names different products give to the same object, you have a significant problem to solve. Four solutions are available to you, as follows:
The best argument in favor of cross platform conventions is the ability to move your code from host to host and to create common code libraries. Because VBA is standardized across the Office suite, VBA code is now quite portable across the products.
If your company does work in both Visual FoxPro and Access 97, having a fairly common set of naming conventions across these platforms will make it easier to move data structures or application objects from one of these platforms to the other.
As defined in this chapter, five components are generally used in naming conventions:
These components are usually arranged in some derivative of the following form:
The brackets indicate optional syntax elements. Notice in the syntax diagram that the tag is required even though the base name is not. Once you adopt a naming convention that uses tags, you cannot mix and match such usage. Either every object has a tag or none have-you should not break this model. Therefore, the tag becomes more critical than the base name, and in a case where the tag is not ambivalent, it can be used by itself.
For example, in the code snippet below, there is only one Database object and one Recordset in the procedure. While a base name on each variable would make the code more clear, especially Cust on the Recordset variable, the tag is obviously even more useful:
Dim dbs As Database
Dim rst As Recordset
Set rst = dbs.OpenRecordset("tblCust", dbOpenDynaset)
Table 5.7 provides a syntax diagram of the various combinations of these components. The diagram will help you understand that some combinations of components can be nonsensical, like putting a puzzle piece in the wrong place. Of course, the terms used for these components and the component layout described in this chapter are suggestions only, derived from my experience. Your naming convention components may not resemble those in the table in every respect. (The spaces between components are added for readability and are not part of the actual usage. The use of upper- and lowercase for the component placeholders is explained following the table.
Table 5.7 Object Name Component Logical Combinations
|BaseName||Without a naming convention, this is all you have.|
|tag BaseName||The simplest and most common construction.|
|BaseName Tag||An alternate approach, to sort by base name.|
|prefix BaseName||This construction would only be useful if you were not ever using any tags.|
|prefix tag BaseName||Provides more detail on the object.|
|BaseName Prefix Tag||An alternate approach, to sort by base name.|
|prefix tag BaseName Qualifier||Provides the most detail on the object.|
|prefix tag qualifier BaseName||A variation on the qualifier location.|
|BaseName Prefix Tag Qualifier||An alternate approach, to sort by base name.|
To each of the items in the table, a suffix could also be added, subject to the discussion in the previous "Using Qualifiers and Suffixes" section.
Notice also in the table the careful use of upper- and lowercase as applied to the component placeholders. I use lowercase tags, prefixes, and qualifiers when they begin the object name, but mix the case on them-as well as qualifiers and suffixes-when they trail the base name. You may choose to employ a different capitalization or punctuation strategy than this.
When prefixes and tags begin the object name, I want to read past them quickly to get to the first (uppercase) character of the base name. For example, I find the name zttblCust easier to read than either ZtTblCust or ZTTBLCust. When any naming conventions, whether prefixes, tags, qualifiers, or suffixes, trail the base name, they may be more readable when mixed case is used. For example, I think that intCustFirst is a more friendly variable name than intCustfirst.
After you define a naming convention, write it down for the benefit of all developers who will be asked to use it. A clearly documented convention (such as the one shown in the following chapter) should leave little room for variance of style between different developers working on the same team or project. Even if you work alone, having a written reference is better than trying to remember a complex set of conventions. If you understand how to use the Windows help compiler included with the Office 97 Developer Edition, you can easily turn your naming convention documentation into a help file. See the file LNC97DEV.HLP on the CD-ROM for an example. Alternately, you could publish your naming conventions on your corporate intranet using HTML documents.
If you adopt or create a set of naming conventions, should you apply it retroactively to any existing applications? The answer depends on the following two questions:
This chapter included many thought-provoking questions for you and your development team. If you intend to standardize naming conventions as a result of this discussion, you may want to answer the questions I posed and prototype a naming convention style now before you proceed further into the development framework issues in subsequent chapters. Alternately, reviewing the following areas of the book will also be helpful as you develop your naming conventions:
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.