Navigation:  Bronze Collection > Mar-1998 > Applying a Naming Convention to a Legacy Application

Helen Feddema          Bronze Collection   
 
One of the ways to make your programs easier to understand is using an effective naming convention. In this article, Helen provides some techniques for applying a naming convention to an existing application.
 
If you're using a naming convention like the Leszynski Naming Convention (LNC) for all of your VBA code, you know how much time and effort it saves by making database objects and variables self-documenting. When you must take over an application developed by another person (or perhaps yourself at an earlier stage of your development career, before you started using a naming convention), you face a daunting choice:

•Continue development in an application where you don't know the data type of most variables without having to go back and find their declaration. In some cases you won't be sure whether a word in code references a function, variable, form, report, field, or control. This is particularly bothersome where the original developer used the same word (say "Sales") as a table name, form name, report name, field name, control name, and variable name; or perhaps used the same variable name (in different procedures) for variables of different data types.

•Spend many hours manually renaming all database objects, controls, and variables to use a standard naming convention, then track down and fix all of the problems caused by the manual renaming. And hope that you got it right.

If you decide to apply a naming convention to the application (definitely a good idea), Access 97 lets you globally search and replace for text strings in modules. Unfortunately, that's only a small part of the renaming needed. The six types of database objects need to be renamed, and the form and report controls as well. To rename the top-level objects, an add-on such as "Find and Replace," SpeedFerret, or the DBRat utility from last month's issue is very helpful. I'll use Find and Replace (FAR) in this article, but you should be able to use other add-ons equally well, as long as they support input of original and replacement object names from a table.
 
Tools
To help automate this activity, I created a set of tables, forms, queries, macros, and a handful of procedures to aid in renaming database objects to a standard convention. I use the LNC, but you can use these techniques with any naming convention. You could even switch a database from one naming convention to another. All of these objects are in download file RENAMING.ZIP.
 
The first step in any activity as significant as this one is to make a copy of your database. You should save it under a name that identifies it as the unchanged version, in case you need to check how something was working before renaming. Then, import all objects from the LNC RENAMING.MDB database into the database you want to rename. While it might turn out that you don't need all of the objects that I've provided, it's easier to not use them if they're there. Tables 1-5 list the database objects used in renaming, with brief descriptions of what they do.
 
Table 1. Tables used in the renaming process.

 
 
Table 2. Queries used in the renaming process.

 
 
Table 3. Forms used in the renaming process.

 
Table 4. Macros used in the renaming process.

 
Table 5 . Module containing routines used in the renaming process.

 
You'll notice that in the list of queries there's a set of select and append queries for each type of database object: tables, queries, forms, reports, macros, and modules. However, you generally won't need them all. You might, for instance, have any macros (except AutoNew and AutoKeys, which can't be renamed). And, generally, you'll only have a few modules, which can easily be renamed by hand and aren't referenced by name elsewhere in the application.
 
The select queries (zsqsel*) make use of the system table MSysObjects to select objects of a specific type. Forms, for example, are listed in the MsysObjects table with a type of 32768, while queries are of type 5. The zsqselForms query is shown in Figure 1.

Figure 1
 
The append queries (zsqapp*) create a new object name by applying a prefix of your choosing to the object's original name. They then append the original name and the new name to the table zstblFAR, to be used for mass renaming with the FAR add-on. If you're dealing with a database that's been partially renamed by hand, you can use criteria to exclude objects that already have appropriate names, as in the modified zsqappForms query shown in Figure 2.

Figure 2
 
Of course, you can also modify the append queries to apply a different naming convention of your choosing.
 
Example: renaming forms
To rename objects of a particular type (say, forms), clear zstblFAR of any existing records, then run the append query zsqappForms. Open zstblFAR and examine the new names in the ReplaceText column. At this point, you can do any needed hand-tuning in this table. You might, for example, want to strip out any spaces in the names or convert forms ending with "Subform" into "fsubFormName." Figure 3 shows zstblFAR as originally filled by the append query for a typical database.

Figure 3
 
To get FAR to do the renaming -- after editing the new form names in zstblFAR as needed -- start FAR and enter "***TABLE:zstblFAR" as the Find string. To make sure that all references to the renamed objects are found and changed, check all the check boxes and click all the command buttons. FAR will locate each instance of each original object name in zstblFAR and let you accept or reject the change. You can also choose to replace without asking, but I don't recommend it. There might, after all, be queries, macros, or some other non-form object with the same original name as your form (as in the "Sales" example I mentioned above). A replace without asking will rename all of those objects along with your forms.
 
Figure 4 shows a case in point: FAR found a reference to "Dealers" in a form module, and suggested changing it to "frmDealers". However, this reference is really to the table "Dealers," as you can guess by its placement in a row source SQL statement, so the proposed change should be rejected.

 
Figure 5 shows a case in which the change is appropriate and should be accepted. Here the appropriateness of the change is evident as the object is a member of the Forms collection.

Figure 5
 
In some cases, it might be difficult or impossible to guess what type of database object is being referenced. You can click the Edit Changes button to see more of the context, and if that isn't enough, leave the reference unchanged. You can also scrutinize the code later on, and try running the code to see what errors occur. If it does generate an error, you can go back to your original copy of the database to see what the name used to be.
 
Here's a tip: In zstblFAR, if you have any original object names that match the beginning of other object names (such as "Sales" and "Sales by Region"), move the shorter name to the end of the table. Since FAR searches down through the table records, when it finds "Sales," it will match it with "Sales by Region." That will give you a lot more proposed changes to reject. If, on the other hand, "Sales by Region" is earlier in the table, by the time FAR gets to "Sales," all the "Sales by Region" matches will have been converted to something else and, when FAR processes the "Sales" matches, it won't find any "Sales by Region."
 
Renaming controls
The next step is to rename controls on forms and reports. This is a more complex operation that requires running a number of append queries. I've also set the process up to handle forms and reports separately, so you must select which group you'll process from a pop-up form. With that done, you then run functions that cycle through the selected forms and reports, renaming all controls that aren't already named correctly and offering the user a chance to edit the proposed control names. The control name renaming functions automatically strip out spaces and non-alphanumeric characters when composing the new control name to avoid problems.
 
When running these functions, you have the choice of saving the old control name to the control's Tag property, as a reference in case the renaming causes any problems later on. Before setting this argument to True, it's best to check the controls in the application's forms to see if the Tag property is already being used in any way. This can be done by searching for the string "Tag" in CBF and modules, using FAR or whatever tool you've chosen.
 
The renaming functions
Now that I've given you a brief introduction to using these tools, I'll walk you through the routines that make it all work.
 
The simplest function is GenerateFormsTable, which simply generates a table of forms and then opens a form displaying those names with a checkbox to check the forms whose controls you want to rename:
 

 
 
The form with its datasheet subform is shown in Figure 6.

Figure 6
 
The command button in the form's footer checks whether any forms have been selected, and if so, runs the RenameFormControls function (otherwise, it pops up a message box and asks if the user wants to select any forms for processing).
 
The GenerateReportsTable function and its supporting query and form are similar, just substituting "reports" for "forms."
 
The RenameFormControls function is more complicated. The function is given below (without standard error-trapping code and Dim statements), with explanation between the sections:
 

 
 
At this point the appropriate renaming function for each type of control must be run (you can find this code in Listing 1). You'll note that in that section I've organized the controls into groups depending on whether they have a control source, caption, source object, or none of the above. Each group has its own renaming function for all of the controls in that group. Within each group, each control has its own case statement, because each has a unique prefix. The control name, suggested prefix, and fTag (which indicates if the original control name is to be saved) are passed to the various control renaming functions. You can edit the suggested prefixes as desired to support your choice of a naming convention.
 

 
 
Next, the user has a choice of saving and closing the processed form, or leaving it open and unchanged for further editing. This is a useful choice if you come across a mystery control and need to examine it further to decide what its name should be.
 

 
 
Finally, Listing 2 provides an example of one of the control renaming functions. The routine I've included is the one used when the control has a control source (the others can be found in LNC RENAMING.MDB in download file RENAMING.ZIP.
 

 
 
Handling problems
Three examples of the renaming functions at work are shown in the following figures. Figure 7 shows a proposed renaming of a standard bound text control whose name is the same as the field to which it's bound. My experience has been, by the way, that the great majority of proposed name changes are perfectly suitable, and can be accepted as is.

Figure 7
 
One type of unacceptable proposed name change occurs when a label has an extraordinarily long caption, as in Figure 8. In this case, the user should just click the no button to open the input box for changing the proposed control name and give it a shorter name.

Figure 8
 
Another special case is a control with an expression as its control source, as in Figure 9. In some cases, you might be able to figure out what name is appropriate from scanning the expression, but not always. In these cases, the best plan is to leave the form or report open after pro-cessing its controls, so you can check out the mystery control and rename it manually. (You'll have the option of leaving the form or report open after all of its controls have been processed.)

Figure 9

Figure 10
 
Variables
For renaming variables in CBF and modules, you can use the built-in Access 97 search and replace functionality, as shown in Figure 10. As tempting as it might be to do a global search and replace for a variable name, it's advisable to limit the search and replace scope to the procedure level, because the same variable name might be used for variables of different data types in different procedures and modules.
 
Before you start renaming, you might find it useful to first print out the module and scrutinize the code to figure out the data type of each variable.
 
Try it yourself
I imported three tables (Categories, Customers, and Employees) from Northwind into the LNC Renaming database. I then ran the Forms Wizard to create a form from each table, and then I ran the Report Wizard to create a report from each table. These six objects should provide you with a test bed for trying out the renaming functions. Renaming these forms and reports will also give you an idea of how the functions work, to prepare you for using the functions in real-world applications.
 
Download RENAMING.ZIP