First Published 12 Mar 2023                     Last Updated 17 Nov 2023                            Difficulty level :   Moderate


Section Links: (this page)
          Introduction
          Name AutoCorrect and Object Dependencies
          Download
          Using the Name AutoCorrect feature with logging
          Propagate Name Changes to all Dependant Objects
          Impact on Performance
          Summary
          Limitations and Solutions
          Feedback



1.   Introduction                                                                                                                                                                       Return To Top

Even with the best planning, there are situations when it makes sense to rename database objects. However, this can cause problems if dependant objects are not also updated in line with any name changes

The Name AutoCorrect feature was designed to handle this situation. It can track object name changes in your databases, and can automatically update the names of objects (forms, reports, tables, queries, fields) or controls, such as text boxes.

When it was first introduced with Access 2000, the feature was unreliable and often caused worse problems. In some cases, it made databases unusable and was quickly nicknamed Name AutoCorrupt by some developers.

Although significant improvements were made in subsequent versions of Access, some developers still do not use the feature or turn it off when not actually currently needed.

I also didn't trust the feature originally but went back to it some years later.
However, for the past 15 years, I have left Name AutoCorrect turned on continuously and found it utterly reliable. I have NEVER had any issues in all that time.

Of course, because I do plan my databases carefully (in theory anyway!), it is only used occasionally within any particular app.
However, I regularly reuse objects in new databases and that is when I will more often rename tables/queries etc

This article will explain how the feature works, its limitations and what you need to be careful about. Following this should also allow you to use the feature successfully in your own applications and save a lot of development time.



2.   Name AutoCorrect and Object Dependencies                                                                                                                 Return To Top

By default, Name Autocorrect is ENABLED in Access Options but logging is DISABLED


With these Name AutoCorrect settings, Access creates a hidden system table called MSysNameMap. This contains name maps of object dependency information for all tables, queries, forms and reports

Normally this is very fast, but when the feature is first switched on, it may take several minutes for a large database


Although the process can be interrupted, it is wise to let it complete in order to generate full name map information. The MSysNameMap system table will look similar to this


Each object is assigned a unique identifier (GUID) and the Type field indicates the object type. The values are similar to those in the MSysObjects table but not always identical:

Object Type	MSysObjects Type	MSysNameMap Type
Table	1 (local) 4 (linked ODBC) 6 (linked Access)	1 (all table types)
Query	5	5
Form	-32768	32768
Report	-32764	32772

The name map of each object is updated each time that you save and close that object. These maps let you to use the Object Dependency task pane to view the list of objects that depend on e.g. a selected table or form, and also view the objects that the table or form depends on.


NOTE: The Object Dependencies pane does NOT include information about:
a)   Macros and code Modules
b)   SQL-specific queries: union/DDL/passthrough
c)   Subqueries - for nested queries, dependency information is generated only for the outermost query.
      This rule also applies to the source tables and queries of a query subdatasheet, and to lookup fields.

Access generally lists these types of query in the Object Dependencies pane under the heading of Ignored Objects > Unsupported Objects.

NOTE: The Microsoft Help article: Use the Object Dependencies pane to see how objects relate includes this paragraph:


The bullet point on action queries (append/update/delete/make table) being excluded from the Object Dependencies pane and hence not covered by Name AutoCorrect is 100% incorrect.

I have tested each of these query types repeatedly.
ALL action queries are included in both the Object Dependencies and Name AutoCorrect features.

The best way to understand how Name AutoCorrect works is to test its use on a non-critical example database repeatedly.
For example, use the attached database which contains 3 tables and 18 queries based on two or more tables



3.   Download                                                                                                                                                                           Return To Top

This is a modified version of the example database supplied with my article: Query Join Types and containing a wide variety of query types

      NAC Test Database v3       Approx 0.8 MB (zipped)



4.   Using the Name AutoCorrect feature with logging                                                                                                        Return To Top

Turn on all three Name AutoCorrect options if not already done:


Choose a table with many dependant queries e.g. Table1. Open the Object Dependencies feature from the Database Tools menu


Open one or more of the dependant objects. Close and rename the table.

Open the Name Autocorrect Log table that is created and confirm that only the opened objects which depend on that table are listed


Open another dependant object. Click F5 to refresh the log table which has been updated with an additional record.


Now rename the table again and open one or more of the unopened queries.


Notice that the Old Value field in MSysNameMap still lists the ORIGINAL table name, Table1.

All changes are referenced to the original object names in the name map no matter how many subsequent changes are made whilst the feature is enabled. This ensures consistency of implementation


Also test by opening the form - this has Table1 as its record source and has a combo with Table2 as its row source


When each object is opened it is successfully updated to the latest iteration of the table names




In normal use, you shouldn't be repeatedly renaming objects in a short period of time. Doing so, indicates poor planning.

Nevertheless, and purely for testing purposes, try doing that for this series of tests

Rinse & repeat the above instructions multiple times without opening every dependant object between each rename.

Although I wouldn't recommend doing this on a working database, it should still work perfectly.


Rename the table(s) again. Close the database and compact.
Reopen and recheck the queries. All should still be working fine

The exceptions to this are unsupported queries e.g. union queries. These have to be updated manually.


In addition, queries that contain errors will also fail e.g. a query with ambiguous joins cannot be opened so the object dependency information cannot be updated


Finally, you should also be aware that Name AutoCorrect does NOT track name changes of form /report controls

NOTE:
If the Name Autocorrect feature is unable to add an item to the Name AutoCorrect Log table, it will be listed in a separate table Name AutoCorrect Save Failures


If the number of log records becomes unwieldy in either table, these can be deleted.
Alternatively the tables themselves can be safely deleted at any time. Access will automatically re-create the tables as necessary



5.   Propagate Name Changes to all Dependant Objects                                                                                                      Return To Top

Name changes are propagated to all dependant objects when they are next opened.
As long as the Name AutoCorrect (NAC) feature remains enabled, this should happen in time without any need for user intervention.

However, you may find the following procedure useful if you wish to disable the NAC feature again after a name change or just want to ensure that NAC propagation has completed. This loops through each table, query, form and report in turn, opens then saves and closes the objects.

The code is provided with the example database. It is in the module modUtilities

Sub PropagateNACObjectChanges()

Dim obj As Object
Dim strName As String
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Dim sngStart As Single, sngEnd As Single

On Error GoTo Err_Handler

      Set db = CurrentDb

      'disable screen updating
      Application.Echo False

      'start timer
      sngStart = Timer

      'tables
      For Each tdf In db.TableDefs
            strName = tdf.Name
            'exclude system tables and deleted tables (~TMPCLP*)
            If Left(strName, 4) <> "MSys" And Left(strName, 2) <> "f_" And Left(strName, 7) <> "~TMPCLP" Then
                  DoCmd.OpenTable strName, acViewDesign
                  DoCmd.Close acTable, strName, acSaveYes
            End If
      Next tdf

      'queries
      For Each qdf In db.QueryDefs
            strName = qdf.Name
            'exclude deleted queries (~TMPCLP"), temp queries (~") used as record / row sources in forms & reports
            'exclude queries that won't run e.g. ambiguous joins("#*)
            If Left(strName, 1) <> "~" And Left(strName, 1) <> "#" Then
                  DoCmd.OpenQuery strName, acViewDesign
                  DoCmd.Close acQuery, strName, acSaveYes
            End If
      Next qdf

      'forms
      For Each obj In Application.CurrentProject.AllForms
            strName = obj.Name
            'exclude deleted forms (~TMPCLP*)
            If Left(strName, 7) <> "~TMPCLP" Then
                  DoCmd.OpenForm strName, acDesign, , , , acHidden
                  DoCmd.Close acForm, strName, acSaveYes
            End If
      Next obj

      'reports
      For Each obj In Application.CurrentProject.AllReports'forms
            strName = obj.Name
            'exclude deleted reports (~TMPCLP*)
                  If Left(strName, 7) <> "~TMPCLP" Then
                  DoCmd.OpenReport strName, acDesign, , , , acHidden
                  DoCmd.Close acReport, strName, acSaveYes
            End If
      Next obj

      'stop timer
      sngEnd = Timer

      'restore screen updating
      Application.Echo True

      MsgBox "NAC Propagation Completed" & vbCrLf & vbCrLf & _
            "Time taken = " & Round(sngEnd - sngStart, 3) & " s", vbInformation, "All done!"

Exit_Handler:
      Exit Sub

Err_Handler:
      If Err = 3296 Then Resume Next      'JOIN expression not supported in query with ambiguous joins
      'Debug.Print "Error " & Err & " " & Err.Description & " in object " & strName
      Resume Exit_Handler

End Sub

The process is very fast




6.   Impact on Performance                                                                                                                                                     Return To Top

As with any monitoring feature, Name AutoCorrect (NAC) does slightly impede system performance.
You may prefer to turn it off completely if your database design is not subject to unplanned design changes.

Having said that, I've never noticed any significant impact even with complex queries based on multiple tables.
Nor have I noticed any slowdown with complex forms containing multiple combobox/listbox row sources as well as form/subform record sources.

Neverthless it was very simple to adapt the above code to run speed comparison tests and determine how great a performance impact the Name Autocorrect feature actually has.

First of all, I ran the above code several times with Name Autocorrect switched off and no name changes.

I then enabled the feature and repeated the tests, firstly with no name changes and then again after renaming all 3 tables used in the queries.

Finally, I enabled the logging feature and repeated the tests, firstly with no name changes and then again after renaming all 3 tables used in the queries.

After each test where name changes took place, I compacted the database as increases in database size will also affect performance.


As expected, using Name Autocorrect caused a small increase in time. Logging name changes as well increased it still further.

However, the extra time needed was certainly far less than doing all the changes manually.



7.   Summary                                                                                                                                                                             Return To Top

After renaming, object dependency information is only propagated when the dependant object is next opened.

For that reason, when using this feature you should do ONE of the following:
a)   EITHER open EACH dependant object in turn to ensure propagation has occurred then switch the feature off
b)   OR leave Name Autocorrect switched on at all times (it will work correctly with or without the logging feature)

The danger with method a) is that if you forget to open each object in turn, dependency information will be lost when the name autocorrect feature is disabled.
As a result, some objects will not work correctly.

I would never try to drive my car when the engine had been partly rebuilt but not finished. Similarly, I wouldn't ever do this with Access

Hence, unless you are extremely careful and well-organised, I STRONGLY recommend using method b)

As shown above, using Name AutoCorrect does slightly impede system performance.
You may prefer to disable the feature if your database design is not subject to unplanned design changes.

However, the above tests indicate that the small impact on performance of the Name Autocorrect feature is vastly outweighed by the time saved compared to making the changes manually



8.   Limitations And Solutions                                                                                                                                                 Return To Top

As well as the unsupported types of query, Object Dependency information and the Name AutoCorrect feature do not work for macros or VBA code in modules.
These all need to be managed separately

In order to simplify the management of ALL database objects, I strongly recommend the use of the following Access tools:

a)   Access Dependency Checker - a free Access add-in which handles all database objects, VBA procedures, custom menus and their dependencies in a single treeview.

b)   V-Tools - another free Access add-in with many features including an excellent global find and replace. This works for all objects (except replace in macros).

c)   Find and Replace for MS-Access - a commercial Access add-in with even more powerful features for managing find and replace. This works for all database objects.

c)   Database Analyzer Pro - a commercial application with a wide range of database analysis features which is available from this website.
      It includes an object dependencies feature which allows you to view all dependencies in a single report and also highlights any missing/broken dependency objects.



9.   Feedback                                                                                                                                                                            Return To Top

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 17 Nov 2023

Return to Access Articles Page

Return to Top