How Name AutoCorrect Works in Microsoft Access

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Frank C. Rice
Microsoft Corporation

September 2002

Applies to:
   Microsoft® Access 2000 and later

Summary: The Name AutoCorrect feature in Microsoft Access 2000 and later is used to automatically correct the side effects that occur when you rename objects in your database. This article will explain the details on how this features works and when it is used as well as provide scenarios to demonstrate its use under different circumstances.

Contents

Introduction
Background
Selecting the Name AutoCorrect Feature
The Name Map
How Name AutoCorrect Works
Scenarios Demonstrating Name AutoCorrect
Best Practices When Using Name AutoCorrect
Conclusion

Introduction

In Microsoft® Access 97 and earlier, if you renamed an object in your database, you risked breaking other objects that depended on the renamed object. For example, let's suppose that you have a number of forms and reports that get their data from a query that, in turn, references several tables. If you renamed the query, in the earlier versions of Access, the dependent forms and reports would break because they referenced the now nonexistent query. To fix the situation, you would have to open each form and report in Design view, open the object's property sheet, and then re-point their RecordSource property to the new query. An even worse scenario could result if you renamed a single field in the query. If some of the forms and reports contained controls that referenced the renamed field, you'd have to go through every form and report to determine which ones contained a control that used the field by opening each one to see which control displayed an error. Then, you'd have to open each of those forms and reports in Design view and update the control to the new field. You can quickly see that a relatively minor change can have far reaching consequences.

Fortunately, in Access 2000 and later, the Name AutoCorrect feature was added. This features works to automatically correct the side effects of renaming most objects, in most circumstances, in your database. The Name AutoCorrect feature works for tables, most queries, forms, and reports in Access .mdb database files.

In this article, we will examine the Name AutoCorrect feature in more detail. Specifically, we will look at when the Name AutoCorrect feature comes into play, and when it doesn't. We'll also look at what happens behind the scenes when the feature is used. More importantly for developers, we'll illustrate through the discussion of various scenarios, situations where Name AutoCorrect may appear to behave irrationally but, with a better understanding of the feature, becomes predictable and can help to make your applications more robust.

In addition to the information provided in this article, other articles are available which may shed more light on using Name AutoCorrect in your database applications. For more information and examples of how to use the Name AutoCorrect feature in your databases, see the article http://office.microsoft.com/assistance/2002/articles/acMysteryNameAutoCorrect.aspx. For information about Name AutoCorrect in databases converted from one version of Access to another, see the article http://office.microsoft.com/assistance/2000/AcAutoCorrectConvert.aspx.

Background

The Name AutoCorrect is a handy feature that automatically propagates name changes for tables, forms, reports, and fields throughout your Access databases.

However, not understanding how and when Name AutoCorrect works has led some developers and users to avoid using the feature in their applications because of perceived "buggy" behavior. For developers, not understanding when and how Name AutoCorrect works can adversely affect the Access applications you deliver to customers. For example, suppose your database application contains two similar data entry forms. One form was imported or converted from an earlier version of Access or was created when the Track name AutoCorrect info option (to be discussed later in this article) was disabled. In this instance, Name AutoCorrect does not have the information that it needs to repair name changes in the form. The other form was created with the Track name AutoCorrect info option enabled thus providing Name AutoCorrect with the information it needs to deal with renaming the form's data source. To the customer, these forms are similar but the long term behavior of the forms will be quite different as field names, query names, or table names change in the application.

It is important to note that the Name AutoCorrect feature is not supported in .adp files. The Name AutoCorrect feature is also not supported in data access pages, macros, Visual Basic® for Applications modules, or in SQL-specific queries such as union queries, pass-through queries, or data definition queries.

Selecting the Name AutoCorrect Feature

The Name AutoCorrect feature is enabled by default in new Access databases. However, if the Name AutoCorrect feature has been disabled in your database, you can enable it with the following steps:

1. On the Tools menu, click Options.
2. In the Options dialog box, click the General tab, and then select or clear one or more check boxes in the Name AutoCorrect area.

Before we begin our discussion of the Name AutoCorrect feature, we need to define a couple of terms that will be used in this article. In an Access database, there are dependent objects, and there are referenced objects. For example, usually when you create a form or report, the data for those objects comes from a table or query. In these cases, the form or report is dependent on the table or query for its data. In addition, the form or report usually contains a reference to the table or query. Therefore, for this article, we will consider the form or report as the dependent object, and the table or query as the referenced object. Objects can also serve dual roles. In the above example, the query was the referenced object for the form but is the dependent object for the table. This notation will become clearer as we progress in the discussion.

The Name Map

When the Track name AutoCorrect info option is selected, Access maintains a name map for each of the supported objects in the database, to include tables, queries, forms, and reports, and the controls on forms and reports.

Note   The name maps are not available for display or modification by the user.

Each object's name map stores its name and a globally unique identifier (GUID). When an object is saved, its name map is updated to also reflect the latest name and GUID of the objects that it references, assuming that the Name AutoCorrect feature is selected. In addition, when you first select the Track name AutoCorrect info option in the Options dialog box, Access opens, examines, and saves the name and GUID for each of the objects supported by the Name AutoCorrect feature in the object's name map.

Note   Although Access will attempt to open and store the name and GUID for each object into the name map when you select the Track name AutoCorrect info check box, it is a good idea to explicitly open and save each object after selecting the Track name AutoCorrect info to ensure the correct information for each object exists in the name map. For example, if user permissions prevent the opening and saving of specific objects, Access won't be able to store the name and GUID of those objects in the name map. Access typically will display an error message if this is the case but it is a good practice to explicitly open and save each object to identify any problems.

One of the check boxes available when you select the Name AutoCorrect feature in the Options dialog box is the creation and use of a Name AutoCorrect Log table. When this check box is selected, each time the Name AutoCorrect feature updates the information in the name map for an object, an entry is also made in the Name AutoCorrect Log table. This table is created in the current database and can be opened and examined by the user. For instance, when Access opens a dependent object, it looks in its name map for the name of its referenced object, and then searches the database for an object by that name. If it is unable to find the referenced object, possibly because the referenced object may have been renamed, Access will again check in the dependent object's name map for the GUID of the referenced object, and then search the database for an object with that GUID. If an object with that GUID isn't found, an error message will be displayed alerting the user to the fact that the referenced object couldn't be found. If an object with the GUID is found, Access will correct the reference to the new referenced object in the name map, and then add an entry to the Name AutoCorrect Log table with the name of the property changed, the old value, and the new value. We will examine this process in more detail in the following sections.

How Name AutoCorrect Works

As stated earlier, Name AutoCorrect fixes discrepancies that arise when you rename forms, reports, tables, queries, or controls on forms and reports. To illustrate how this is done, let's look at a simple example (see Figure 1). Suppose that you create a form Form1 (dependent object) that gets its data from a query Query1 (referenced object). Likewise, the query Query1 (dependent object) gets its data from table Table1 (referenced object). When you create these objects with the Track name AutoCorrect info check box selected, Access adds entries to each dependent object's name map for each of their referenced objects. Now, let's examine the sequence of actions that Name AutoCorrect initiates if you rename the query from Query1 to Query2. In the steps below, the step numbers are represented by the numbered callouts in the image.

Figure 1. Name map actions when renaming a query

1. The objects are created and the name maps are populated with the dependent object's name and GUID as well as the name and GUID for each referenced object.
2. You rename the query Query1 to Query2. The next time you open the form Form1, Access fails to find its referenced object, Query1, so it tries to use Name AutoCorrect.
3. Since Query1 no longer exists in the database, Access looks in Form1's name map for the GUID of Query1, and then searches each object in the database until it finds an object whose GUID matches. In this instance, because we renamed Query1 to Query2 (instead of creating Query2 by another means such as copy, pasting, and renaming Query1, or by creating Query2 from scratch), Query2 retains its original GUID, and Access finds Query2 as a replacement for Query1.
4. Name AutoCorrect changes the record source of form Form1 to Query2, modifies the name map to reflect this change. Access then continues opening Form1, displaying the correct data. An entry is also made in the Name AutoCorrect Log table of the change in the record source for Form1.

It is worthwhile to note that the relationship of one object to another, as displayed in the Relationships dialog box in Access (click Relationships on the Tools menu), and the relationships of objects stored in the name map can be out of synchronization with each other. Changes made to the way objects relate to each other in the Relationships dialog box take affect as soon as the user saves the changes in the dialog box. However, as we will see in the following sections, changes to the way dependent and referenced objects are related to each other in the name map are only updated when the dependent object is explicitly opened.

Scenarios Demonstrating Name AutoCorrect

Let's consider other scenarios and see how the Name AutoCorrect feature handles these situations. Let's assume that you have the following objects in your database: a form (Form1) containing a control (Control1). Form1 is dependent on a query (Query1) which, in turn, is dependent on a table (Table1). Control1 gets its data from one of the fields in Query1. Now, let's step through a couple of scenarios with diagrams.

Name entries in the name map have precedence over GUID entries. To illustrate how this works, step through the following scenario (see Figure 2).

Figure 2. Name map actions when creating new table with same name

1. The objects are created and the name maps are populated with the dependent object's name and GUID as well as the name and GUID for each referenced object.
2. Next, you rename Table1 to Table2, and then create a new (but different) Table1.
3. When you open Query1, Access searches its name map and finds that the record source for Query1 is Table1. Access then searches the objects in the database and finds Table1.
4. No action is initiated by Name AutoCorrect. And although the user meant that the correct record source for Query1 (and for Form1) should be Table2, Name AutoCorrect chooses Table1 because the names match. When the user opens Form1, the form displays errors because it is referencing the wrong data source.

The match occurred in spite of the fact that the GUIDs stored in the name maps for the new Table1 and for Query1 didn't match. Only when Name AutoCorrect can't find an object with the correct name will it use the GUID to find a match. The Name AutoCorrect feature isn't concerned that the data in Table1 may be incompatible with the controls in Form1. Name AutoCorrect seeks to match names to names, and when that fails, match GUIDs to GUIDs.

Let's walk through another scenario for more insight as to how Name AutoCorrect works (see Figure 3).

Figure 3. Name map actions to changes in a control on a form

In this scenario, we will look at the effect of renaming the control source for the control Control1:

1. The objects are created and the name maps are populated with the dependent object's name and GUID as well as the name and GUID for each referenced object.
2. You open Form1 in Design view, and then change the control source of Control1 to another field in Table1. Because the field exists in Form1's record source (Table1), and is thus available for use by Control1, no action is necessary by Name AutoCorrect. In addition, because no discrepancies are found by Name AutoCorrect (in other words, the field does exist in the record source), no entry is added to the Name AutoCorrect Log table.
3. You verify that this is correct by opening Form1 in Form view. Although Control1 may display incorrect data, to the Name AutoCorrect feature, this change requires no action.
4. Next, you open Form1 in Design view and change the control source for Control1 to a non-existing field value.
5. You, then, open Form1 in Form view. Control1 displays an error but, because the referenced object for Control1 (Table1) didn't change, no action is necessary by Name AutoCorrect and no entry is added to the Name AutoCorrect Log table.
6. Then, you reset the control source of Control1 back to the original field value (Table1:Field1) which returns Form1 to its original configuration.
7. Next, you open Table1 in Design view, change the name of the field that is the control source for Control1 (Field1) to a different name (New), and then open Form1 in Form view. Name AutoCorrect searches Table1 for Field1 but doesn't find it. Name AutoCorrect then goes back to the name map for Control1, determines the GUID for Field1, and then finds that value in Table1 which is associated with the new field (New). Name AutoCorrect updates the name map for Control1 to point to the new field, and then adds an entry to the Name AutoCorrect Log table.
8. Access then finishes opening Form1. Control1 displays the contents of the new field.

Best Practices When Using Name AutoCorrect

Problems can arise when renaming referenced objects. Remember that although the user may give the renamed objects a new name, the original GUID is retained in the name map. Consider the following example, with two forms: Form1 and Form2, and a table Table1 (see Figure 4).

Figure 4. Possible situation when incorrectly renaming referenced objects

1. The customer creates Form1 and Form2, both of which get their data from Table1. The customer select the Name AutoCorrect feature and the name maps are populated.
2. The customer renames Table1(GUID:100) to Table2(GUID:100).
3. The customer opens Form1. Name AutoCorrect silently changes the form's reference to Table2(GUID:100). Form1 works correctly.
4. Later, the customer creates new Table1(GUID:200), and then opens Form1 which points to Table2(GUID:100). Form1 works correctly.
5. The customer then opens Form2 which points to Table1(GUID:100). Form2 doesn't work as expected and the customer is confused.

In order to avoid this and similar situations, there are a few best practices that you should follow when working with the objects in your database.

When archiving a table, copy and paste the table with new name:

1. In the Database window, click to select the table, and then on the Edit menu, click Copy.
2. Click the Edit menu, and then click Paste.
3. In the Paste Table As dialog box, type a new name for the table. This action will add an entry to the name map for the table with a new name and a new GUID.
4. And finally, create and run an Update query on the original table to remove the existing records.

By using this procedure, Name AutoCorrect won't erroneously point any dependent objects to the archived table.

Similarly, to replace a form or report that has become corrupt or has other problems:

1. Create a backup copy of the form or report. In the Database window, click to select the form or report, and then on the Edit menu, click Copy.
2. Click the Edit menu, and then click Paste.
3. In the Paste As dialog box, type a new name for the form or report. This action will add an entry to the name map for the form with a new name and a new GUID.
4. Delete the original form and create from scratch.

Conclusion

In this article, we examined the Name AutoCorrect feature is some detail. We looked at different scenarios to determine when Name AutoCorrect was utilized and when it did not come into play. We also outlined a few best practices when working with objects in your database. I would encourage you to use the Name AutoCorrect feature and explore ways that it can help you and prevent problems in your database applications.