First Published 12 Mar 2023 Last Updated 17 Nov 2023 Difficulty level : Moderate
Section Links: (this page)
Name AutoCorrect and Object Dependencies
Using the Name AutoCorrect feature with logging
Propagate Name Changes to all Dependant Objects
Impact on Performance
Limitations and Solutions
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|
4 (linked ODBC)
6 (linked Access)
|1 (all table types)|
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
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
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
sngStart = Timer
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
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
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
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
sngEnd = Timer
'restore screen updating
MsgBox "NAC Propagation Completed" & vbCrLf & vbCrLf & _
"Time taken = " & Round(sngEnd - sngStart, 3) & " s", vbInformation, "All done!"
If Err = 3296 Then Resume Next 'JOIN expression not supported in query with ambiguous joins
'Debug.Print "Error " & Err & " " & Err.Description & " in object " & strName
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