Avoid Errors with Access' Name AutoCorrect Feature

In earlier versions of Access, it's important to get an object's name right the first time. For example, if you name a table field FirstName, and later decide to call it FName, you must find and replace every occurrence of the old name throughout your database--in queries, forms, reports, macros, and modules. And as always, there's that one occurrence that you don't find until later which rears its ugly head as #Name, or #Error, or even as a new parameter in your form or report.

People have developed all sorts of inventive workarounds to make changing object names easier--including several third-party utilities. Now, Access 2000 can handle these alterations with its new Name AutoCorrect feature. Using this feature, you can change the name of objects in your database and Access will substitute the new name for the old one in all database objects--kind of like an update query for your database. In this article, we'll discuss what this feature will and will not do when it comes time to make name changes in your database.

Name AutoCorrect's features

By default, whenever you create a new database, Access 2000 turns on the Name AutoCorrect options shown in Figure A. You find these selections on the General tab of the Tools/Options menu item.

Figure A: When you create a new database, Access 2000 automatically turns on the Name AutoCorrect feature.
[ Figure A ]

When you choose the Track Name AutoCorrect Info option, you tell Access to store the information it needs to track name changes. However, it doesn't actually make those changes unless you also select Perform Name AutoCorrect. When you select this option, Access doesn't change every occurrence of the name in every object right away. Instead, it makes changes on a need-only basis. That is, it waits until you actually use an object, and then makes the necessary changes. After it's finished with the corrections, Access saves the object. Of course, if you don't have the correct security permissions, Access 2000 can't save the object. Fortunately, though, it still updates the name each time you use the object.

When you want to keep a permanent record of the modifications that Access has carried out, select the Log Name AutoCorrect Changes option. Access keeps this log in a table aptly called Name AutoCorrect Log. Now that we've covered this new feature's options, let's take a closer look at how they work.

A Name AutoCorrect example

To illustrate some of the Name AutoCorrect's advantages, we'll create a simple query and form; then, we'll change the name of a field in their underlying table. We'll track how Access 2000 handles this name change as we turn on each successive AutoCorrect option in the Name AutoCorrect panel of the Options dialog box.

To begin, open a blank database and name it AutoCorrect. Now, let's quickly grab the Customers table from the Northwind sample database. To do so, select File/Get External Data/Import from the menu bar. When Access displays the Import dialog box, select the Northwind.mdb file, which you can usually find in the C:\Program Files\Microsoft Office\Samples\ directory. Next, choose the Customers table and click OK to import the table into our database. Now, let's create a query and a form based on this table.

Create the sample query and form

To create the query, select the Customers table in the Database window and select Query from the New Object button's dropdown menu. In the New Query dialog box, double-click Design View. Access opens the Query By Example grid. Now, drag down CompanyName and Address from the Customers' field list. That's all we need for our example, so close the query. Click Yes to save it, and at the prompt, name it qryCorrect.

At this point, we'll create the form. To do so, click the Tables button in the Database window, and select the Customers table once more. Now, choose Form from the New Object button's dropdown menu. Double-click Form Wizard from the list of selections in the New Form dialog box. Add CompanyName and Address to the form, and then click Finish. When you do, Access builds a form with two fields on it. Close the form and select the Forms button on the Database window. By default, Access named the form Customers. To make things easier on us, let's rename it. To do so, right-click on Customers in the Database window and select Rename from the resulting shortcut menu. Name the form frmCorrect. At this point, we're ready to test the Name AutoCorrect feature.

Track Name AutoCorrect info

To begin, return to the Options dialog box (select Tools/Options from the menu bar, and select the General tab) and deselect all but the first Name AutoCorrect option, Track Name AutoCorrect Info. Click Apply and then OK to close the dialog box. Often, when you design a database, you may want to shorten lengthy field names, such as CompanyName to CompName. Let's make that change now in the Customers table.

Select the Tables button on the Database window, select the Customers table, and click the Design button. When Access shows the table's Design view, change the CompanyName field to CompName, as shown in Figure B.

Figure B: We changed the CompanyName field to CompName.
[ Figure B ]

Now, switch to the Queries section of the database and double-click qryCorrect to run the query. When you do, Access prompts you for a Customers.CompanyName parameter value. That's because, as we stated earlier, even though we're tracking name changes, Access still doesn't update them. Click the Cancel button to prevent the query from executing, and then click the Design button on the Database window. As you can see in Figure C, the QBE grid contains the wrong name, which is what you'd normally expect. Now, close the query without saving the changes and let's see what happens when we choose to perform the name corrections.

Figure C: Even with the Track Name AutoCorrect option turned on, Access doesn't update any database objects to reflect the new name.
[ Figure C ]

Perform Name AutoCorrect

Return to the Options dialog box, and select the Perform Name AutoCorrect option. Click the Apply button, and then click OK. At this point, Access is ready to perform name changes, but will not actually do so until we open an object. This makes sense when you consider the performance hit Access would take if it had to cycle through every object in a large database every time you changed a name.

Switch back to the qryCorrect query's Design view. This time, Access changed the name, as you can see in Figure D. Now, we'll see how Access logs all of these automatic corrections.

Figure D: The AutoCorrect feature changed the field name from CompanyName to CompName in the qryCorrect query.
[ Figure D ]

Log Name AutoCorrect Changes

Close the qryCorrect query and select the Log Name AutoCorrect Changes check box. Click the Apply button once more and return to the Database window. You probably noticed that in the Customers table there's only one Address field. Often, you'll want to have two of these fields, Address1 and Address2. We won't go to the trouble of actually adding a second address field, but let's rename the existing field so that Access can log the change. To do so, open the Customers table in Design view and change Address to Address1. Close the table and save the changes.

At this point, don't expect to see a Name AutoCorrect Log table. Access creates the table when it makes its first adjustment to the database, so let's have it make a correction. Switch to the Forms section of the database and open frmCorrect in Form view. Access displays the form without a hitch. (Notice, however, that the text box's caption hasn't changed; it still says Address. If for some reason we wanted the caption to reflect the field's new name, we'd need to change it manually.) Since we opened an object that needed alterations, we can now view the table of logged corrections.

The log table

Close the frmCorrect form and switch to the database's Tables section. The Name AutoCorrect Log table now appears in the list. Double-click on it to view its contents. Figure E shows the table and the logged changes. As you can see, Access made the necessary modifications to both controls when it opened the form.

Figure E: Access created entries in the log table for each modification it made.
[ Figure E ]

As with the form's caption, even though Access changed the control's ControlSource property to reflect the new field name, it didn't change the names of the controls. These remain the original Address and CompanyName--another instance where you'll need to make changes manually if you want the control names to be in sync with their underlying field names.

In addition, Access doesn't log the changes it makes to queries. To see this for yourself, close the log table and run the qryCorrect query. Since we changed the name of Address to Address1, Access makes the appropriate modifications to the query's design and the query runs without any problems. However, when you re-open the Name AutoCorrect Log table, there isn't a record to reflect this alteration.

Some AutoCorrect caveats

While this feature is automatically turned on for all new Access 2000 databases, it isn't the default selection for converted databases. For those, you'll need to choose these options manually. Also, you won't be able to use Name AutoCorrect at all in replicated databases.

Conclusion

In earlier versions of Access, it's very frustrating trying to track down all of the occurrences of a name that you've changed. Thankfully, Access 2000 alleviates some of this hassle with the new Name AutoCorrect feature. In this article, we've shown you how it works.

Copyright © 1999, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.