Chapter 9: Reworking an Existing Application

When you are given the task of reworking an application developed by another person (perhaps a client with only basic end-user Access skills), you have to make a decision on whether to rework the database or scrap it and start over from the beginning. If the database does work (mostly), you can rework it—apply a naming convention, normalize tables and relationships, create an attractive main menu, and standardize the appearance of forms and reports. If it is basically nonfunctional, you will need to create a new database, and then move data from the old, nonworking database into the new one (this task is covered in Chapter 10, Moving Old Data into a New Database). In this chapter, I will concentrate on fixing and enhancing a database that is basically functional, but needs work to become an efficient and attractive application.

I’ll explain why a naming convention is essential for making your database self-documenting, and walk you through using the LNC Rename add-in I created to make it easier to apply a naming convention. I will also explain (using a real-life example) how to normalize a database’s tables, and rework its forms and reports as needed.

Applying a Naming Convention

Access lets you give database objects (tables, queries, and so forth) any name you want, up to 64 characters in length, with spaces and most punctuation marks. Unfortunately, Access does not automatically apply an appropriate prefix to objects as you create them (a feature that has long been requested by developers!). Considering that the original Leszynski-Reddick Naming Convention (the predecessor to the Leszynski Naming Convention) was developed for Access 1.0 in 1993, autoprefixing of database objects is long overdue. However, Microsoft hasn’t yet built this essential feature into Access, so it is up to the user (or developer) to manually apply a naming convention to database objects and variables.

While freestyle Access object and control naming is certainly an improvement over older database programs (such as dBASE), which had strict limits on object and field name length and format, this flexibility has a downside. In the interface, you can see what type of object you are dealing with, because Access places different types of objects on separate pages in the database window. For queries (as shown in Figure 9.1), you can tell the query type by its icon, where a union query has the linked circles icon, and a crosstab query has the datasheet icon, instead of the standard select query linked datasheets icon.

Figure 9.1

However, when you are working in VBA code—referencing database objects and creating and using variables—the lack of a built-in identifier causes serious problems. There is no way to tell whether the word “Sales” refers to a table, query, form, report, or variable (and if a variable, what data type). Additionally, when you are working with forms and reports, especially those created by the Access Form Wizard or Report Wizard, typically all bound controls have the same name as their fields (see Figure 9.2), and so do controls that you manually drag to a form or report in Design view. Unfortunately, this causes reference confusion and, in some cases, circular reference errors.

Figure 9.2

Important

If you see #Error in a control in form view, this is most likely the result of a circular reference error, caused by a control having the same name as the field to which it is bound. The circular reference occurs when an expression in a control’s Control Source property references the field to which the control is bound, while the control itself has the same name. See Figure 9.3 for an example of an expression that will cause #Error to appear in a control. The fix is simple: Give the control a prefix so that it has a different name than the field.

Figure 9.3

When creating a database from scratch, you can (and should) give all newly created objects, controls, and variables names that indicate (respectively) the object, control, or data type. The Leszynski-Reddick Naming Convention (1993) was the first widely accepted naming convention for Access, and is based on the Hungarian notation for naming objects developed by Charles Simonyi. There are now two variants of the original convention: the Leszynski Naming Convention (which I use) and the Reddick VBA Naming convention. The important thing is not which naming convention you use, but that you use one If your database will be viewed by other developers, it is advisable to use one of the two widely accepted conventions (Leszynski or Reddick).

The latest version of the LNC may be downloaded from www.kwery.com and the latest version of the RVBA convention from www.xoc.net.

The Leszynski Naming Convention

The Leszynski Naming Convention uses prefixes to indicate the object type, control type, or variable data type, and (optionally) the field data type. Use of distinctive prefixes makes your database self-documenting; when you see frmSales in code, you will know that it is a form, and when you see curSales you will know that it is a Currency variable. When selecting a query from a drop-down list, you will know that a query starting with qmak is a make-table query, while one starting with quni is a union query. This information will allow you to avoid errors such as using the SetFocus method (a control method) with a field, or trying to open a table.

Hungarian notation (named after Charles Simonyi’s native country) breaks down object names into the following elements:

 [prefix(es)][tag]BaseName[Suffix/Qualifier] 

These elements are described in the following table.

Component	Description	Example
Prefix(es)	A lowercase letter that adds extra information to the tag.	p for Public variable
Tag	A three-letter sequence indicating the object type.	tbl for table
BaseName	A word or two describing the object. If several words are used, each starts with a capital letter, and there are no spaces between them.	OutlookContacts —contacts imported from Outlook
Suffix (RVBA), Qualifier (LNC)	A word giving more specific information about an object.	ByDate —the data is sorted by date

I use LNC notation for database objects, controls on forms and reports, and variables. I don’t use field prefixes (although some developers do), because giving prefixes only to controls prevents confusion—if a name (such as StreetAddress) has no prefix, I know that it is a field name. Also, using field prefixes would interfere with the operation of my LNC Rename add-in (to be described later in this chapter).

The basic LNC object prefixes are described in the following table.

Object	Tag
Incomplete objects, backup objects, or objects that are under development	_ or– (In Access 2000 and 2002, dashes sort to the beginning of the database object list; in Access 2003, underscores sort to the beginning)
Hidden system objects	zh
Displayed system objects	zs
Programmatically created temporary objects	zt
Backup copies of objects, for later copying or reuse	zz

The basic LNC variable prefixes are described in the following table.

Variable type	Tag
Local variable	[no prefix]
Local static variable	s
Module-level variable	m
Public variable in a form or report module	p
Public variable declared in the Declarations section of a standard module	g

The basic LNC database object tags are described in the following table.

Object	Tag
Class module	cls
Form	frm
Form (dialog)	fdlg
Form (menu)	fmnu
Form (message)	fmsg
Form (subform)	fsub
Macro	mcr
Module	bas
Query (any type)	qry
Query (append)	qapp
Query (crosstab)	qxtb
Query (data definition)	qddl
Query (delete)	qdel
Query (form filter)	qflt
Query (lookup)	qlkp
Query (make-table)	qmak
Query (select)	qry (or qsel)
Query (SQL pass-through)	qspt
Query (union)	quni
Query (update)	qupd
Report	rpt
Report (subreport)	rsub
Table	tbl
Table (attached dBASE)	tdbf
Table (attached Excel)	txls
Table (attached FoxPro)	tfox
Table (attached Lotus)	twks
Table (attached ODBC)	todb
Table (attached Paradox)	tpdx
Table (attached SQL Server)	tsql
Table (attached text)	ttxt
Table (lookup)	tlkp

The following table lists the LNC table field tags (I don’t use these personally, but I am including them for completeness).

Object	Tag
Autonumber (random nonsequential)	idn
Autonumber (replication ID)	idr
Autonumber (sequential)	ids
Binary	bin
Byte	byt
Currency	cur
Date/Time	dtm
Double	dbl
Hyperlink	hlk
Integer	int
Long	lng
Memo	mem
OLE	ole
Single	sng
Text (character)	chr
Yes/No (Boolean)	bln

The following table lists the LNC tags for VBA variables.

Object	Tag
Combobox	cbo
CommandBar	cbr
Control (generic; useful when cycling	ctl_through controls on a form or report)
Currency	cur
Database	dbs
Double	dbl
Form	frm
Integer	int
Label	lbl
List box	lst
Long	lng
QueryDef	qdf
Report	rpt
Single	sng
Snapshot	snp
String	str
Table	tbl
Textbox	txt
Type (user-defined)	typ
Variant	var

The LNC tags for form and report controls are listed in the following table.

Object	Tag
Bound object frame	Frb
Chart (graph)	Cht
Check box	Chk
Combobox	Cbo
Command button	Cmd
Custom control	Ocx
Frame	Fra
Hyperlink	Hlk
Image	Img
Label	Lbl
Line	Lin
Listbox	Lst
Option button	Opt
Option group	Grp
Page (on a tab control)	Pge
Page break	Brk
Rectangle (shape)	Shp
Subform/report	Sub
Tab control	tab
Textbox	txt
Toggle button	tgl
Unbound object frame	fru

The following table lists some typical LNC names of database objects, controls, and variables.

Object/variable name	LNC naming elements	Description
tblEmployees	tag + base name	A table of employee data
qupdSales	tag + base name	A query that updates sales data
fsubDayMax	tag + base name + qualifier	A subform that shows the maximum day
intLines	tag + base name	An Integer variable to hold a value representing the number of lines
curSales	tag + base name	A Currency variable holding a Sales value
pstrForm	prefix + tag + base name	A public String variable holding a form name
zztblContacts	prefix + tag + base name	A backup Contacts table, for copying and filling with imported data

In addition to the standard LNC tags, you can create your own custom tags as needed. I use the tmak tag for tables created from make-table queries, with the same base name, so when I see a table called tmakNewContacts, I know that it was created by a query called qmakNewContacts. This lets me know that if I want to modify the table, I need to modify the query that generates it, because changes to the table will be lost the next time the query is run. I also use qtot as a prefix for totals queries—although totals queries are just a type of select queries, when you are selecting a query to use as the source object for a totals subform, it is useful to know which queries are totals queries.

Applying the LNC to a Database

When you are reworking an application created by someone else, with no naming convention, applying a naming convention manually is a daunting (and time-consuming) task—and also one that a client probably doesn’t want to pay for! You not only have to rename each object, and each form or report control, but also have to correct all the references to the renamed objects and controls in other database objects and code. To make this task less burdensome, I created the LNC Rename add-in, an Access add-in that automates the process of renaming database objects and form and report controls, and also corrects references to them in code.

This add-in piggybacks on another add-in, Rick Fisher’s very useful Find And Replace, for some of its functionality (FAR does the search and replace work in code). The latest version of Find And Replace may be downloaded from Rick Fisher’s Web site, www.RickWorld.com.

If you don’t already have LNC Rename installed, you will need to install it. This add-in is available as LNC Rename Add-in.zip (for Access 2000 and up) from www.wrox.com. The .zip file for this add-in includes a 30-day trial version of FAR. To install FAR, extract the Repl9.exe file and follow the instruction in its Readme file. After installing FAR, and copying the LNC Rename.mda file to the Add-ins folder (usually C:\WINDOWS\Application Data\Microsoft\AddIns), you can install LNC Rename from the Add-in Manager in any database, as shown in Figure 9.4.

Figure 9.4

The sample Northwind database that comes with Access has no consistent naming convention, so it is a good example for running the LNC Rename add-in. For trying out the LNC Rename add-in, make a copy of the Northwind database (located in the Samples subfolder under the main Office folder); alternately, you can use the sample database in the LNC Rename Add-in.zip file.

After installing the LNC Rename add-in, you will now have three new selections on the Add-ins menu: Rename Form Controls, Rename Report Controls, and Rename Database Objects, as shown in Figure 9.5.

Figure 9.5

LNC Menu Add-Ins

The first step in renaming objects is to rename the main database objects. In the copy of Northwind.mdb, drop down the Add-Ins menu and select Rename Database Objects. The Choose Database Object dialog opens, as shown in Figure 9.6.

Figure 9.6

I recommend doing the renaming in the following order: tables, queries, forms, reports, and (optionally) macros and modules (you may not feel the need to rename macros and modules, because they are rarely referenced by other database objects). After selecting a database object type from the option group, click the Rename Database Objects button to open another dialog, listing each object of the selected type in the database with its current name and suggested new name. Figure 9.7 shows this dialog for tables.

Figure 9.7

You can edit the suggested new names as desired, and (if you don’t wish to rename all the objects), uncheck the Use checkbox for selected objects. Clicking the Rename Checked Tables button starts the process of renaming the tables and all references to them. In a database with no naming convention (such as Northwind), the same name is typically used for several different types of database objects, such as tables and forms, so you will need to verify all the proposed name changes. The first confirmation dialog that appears when renaming Northwind tables is shown in Figure 9.8.

Figure 9.8

You wouldn’t want to change the word “Categories” in the table description to “tblCategories,” so click No on this dialog (and any other dialogs that would make an inappropriate change). Figure 9.9 shows an appropriate reference change, in the row source of a lookup field.

Figure 9.9

Some object renaming hints:

• SELECT clauses reference tables or queries.

• The IsLoaded function references forms.

• Forms! references reference forms.

• Reports! references reference reports.

• Text in Description fields and label captions generally doesn’t need renaming.

• Source objects are forms or reports.

• Record sources and row sources are tables or queries.

When renaming forms, I like to use the tag fsub for subforms, and similarly rsub for subreports.

When renaming queries, the query type may not be apparent from its name. If you select the Queries tab of the database window first, and size it so you can see all the queries, it will be a handy reference when renaming queries because you can tell the query type from its icon. See Figure 9.10 for an illustration of this technique.

Figure 9.10

After renaming all the database objects, the next step is to rename controls on forms and reports. Some developers don’t bother with renaming report controls, because they are rarely referenced elsewhere in the database, but personally I like to give them correct names too—and you should at least rename any report controls that are referenced in code, for example in a Format event procedure.

To rename form controls, select the Rename Form Controls selection on the Add-ins menu; the Forms to Rename dialog opens (as shown in Figure 9.11), listing all the forms in the database. If you don’t want to rename controls on all the forms, just uncheck the ones you want to skip, and then click the Rename Controls on Checked Forms button to start renaming controls on the selected forms.

Figure 9.11

The process of renaming controls starts with a question about whether you want to save the original control name to the control’s Tag property. Saving original names to the Tag property is a good idea if you are reworking a complex application that has references to controls in VBA code or elsewhere. In case you find a mystery reference in code, you can search for the original control name in the Tag property using Find and Replace.

Next, you will get a series of dialogs, each displaying the current control name and the proposed new name, using the appropriate LNC control tag, and removing spaces and most punctuation marks (see Figure 9.12). Most of the proposed names can be accepted as is, but occasionally you will come across a control that can’t be renamed automatically, such as controls with calculated expression control sources, or labels with long captions.

Figure 9.12

After each selected form’s controls have been processed, you will get a confirmation message asking if you want to save and close that form; if you have any questions about renaming objects on a form, click No to leave the form open in Design view, so you can review the name changes before closing it; if all the changes are fine, click Yes on this dialog. You will get a confirmation message when all the forms have been processed.

Renaming of report controls is similar, so I will not discuss that process.

LNC Rename Property Wizards

In addition to the three menu add-ins discussed previously, LNC Rename also includes some Property Wizards that let you rename an individual control or all controls on the currently open form or report. To rename an individual control (perhaps one newly added to a form), select the control in Design view, open its properties sheet, and click the Build button beside the Name property (it’s the tiny button with three dots), as shown in Figure 9.13.

Figure 9.13

The Choose Builder dialog opens, as shown in Figure 9.14, where you can see two LNC Rename Property Wizards (and perhaps others you may have installed).

Figure 9.14

Select the LNC Rename Current Control builder to rename the current control. You will get a dialog like the one shown in Figure 9.15, where you can select Yes to accept the proposed name or No to edit it.

Figure 9.15

The process of renaming a single control sounds more complex than it is—in most cases, all you need to do is click the Build button and press Enter twice, to rename a control with the standard tag.

To rename all the controls on a form that is open in Design view, select the Detail section, open its properties sheet, click the Build button next to the Name property to open the Choose Builder dialog, and this time select the LNC Rename All Controls selection. Dialogs for all the controls on the form will open in turn, letting you rename all the controls on the form.

Important

Access 2000 added an AutoCorrect feature that many developers hoped would make it easier to rename database objects, but unfortunately this feature has several serious flaws, which make it practically unworkable. Basically, AutoCorrect fixes some (but not all) references to renamed objects, and none of the references in VBAcode, which makes it pretty much worthless because you will have to use a utility such as FAR to correct the missed references. It’s easier to just use FAR directly.

Additionally, AutoCorrect has a very annoying feature of “correcting” references that should not be corrected. For example, if you are working on the design of a form called frmOrders, and you make a backup copy of the form, called -frmOrders1, you will find that references to frmOrders (say, in a query criterion) may be changed to -frmOrders1, resulting in errors.

I recommend turning off AutoCorrect in the General tab of the Access Options dialog and using LNC Rename and/or FAR to rename database objects as needed.