Database Bloat

[isladogs]

As previously stated

... many people turn it off when not actually currently needed.

 

[Pat Hartman]

The problems that existed in the beginning still exist with Name Auto Correct. Because of this I ALWAYS turn it off unless i am going to make changes I want it to propagate.

The issue is that people assume that if you change the name of a query, Access then goes and finds all the affected objects and changes them at that point. However, that isn't what happens. What happens is that Access makes a log entry into a table to record the change (which is why if I turn on NAC, I ALWAYS show this table also). When an object is opened, and there is a problem with something "missing", Access checks the Name Auto Correct log table and if the item is found, it applies the change to the open item but NOT any others.

It could be weeks before all affected objects get updated with the new name and therein lies the problem. What if you make a change on top of a change that hasn't been applied to all objects? And another one on top of that? It becomes very difficult to follow the path and manage to get everything updated correctly. What if you then turn off the feature before all changes have been applied? Access just stops applying changes. Now you're really hosed.

I get around this by always opening and closing every potentially affected object before I turn off NAC when I am done.

One really dangerous practice when using NAC is renaming an object and then changing the original. What happens here is qABC is renamed to qABCold. If you then open an object using that query, it is quietly updated to qABCold. Now you change qABC with your updates but the form/report/query isn't using the new version of qABC, it is using qABCold instead.

 

[ebs17]

Nice description.

Wizards that tinker around should be fully understood and used only within their intended capabilities. What the developer of this assistant has thought and what a user thinks it should do, there can be huge differences.

 

[isladogs]

@Pat Hartman
Yes, its a great description but with respect I don't believe its completely correct for several reasons:

1. When Name AutoCorrect was first created in A2000(?) it often caused corruption and many developers, not least yourself, often called it Name AutoCorrupt for good reason. However, those issues were fixed in the next release

2. It is certainly true that closed objects do not appear in the Name AutoCorrect Log after a rename is done. They do appear when the object is next opened. However, I would say that is masking what is actually happening as can be tested very easily

Turn on all 3 Name AutoCorrect options
Select a table with many dependant queries. Open the Object Dependencies feature
Open one or more of the dependant objects and then rename the table.

Open the Name Autocorrect Log table and confirm that only the opened objects are listed
Open another dependant object. The log table is updated with an additional record.
So far, we are largely in agreement....

Now rename the table again and open one or more of the unopened queries.
Each is successfully updated to the latest iteration of the table name

Rinse & repeat multiple times. It works perfectly.
In my tests, the issues you describe no longer occur (I accept they did so back in A2000)

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

To help with testing, I am attaching a test database used with one of my web articles on query joins
It has 3 test tables & lots of different queries. I've enabled all 3 NAC options

If any of the above summary is incorrect, do let me know

 

[Pat Hartman]

Did you try turning off Name auto correct before all updates had been applied? It was about 10 years ago that I worked through all of this so things might have changed. The point I was making is that you CAN control name auto correct if you understand that its change propagation does not take place immediately. It doesn't happen until the NEXT time an object is opened AND I wouldn't turn off name auto correct until I was certain that all changes had been propagated.

 

[isladogs]

Nor would I turn it off part way through as it would obviously cause problems.
Similarly, I wouldn't try to drive my car when the engine had been partly rebuilt but not finished

When NAC was first released, I had the same opinion as you and switched it off.
Several years later, I went back to it & have used it with ZERO issues for about 15 years.
As a result, although I didn't say so earlier, I leave it on all the time and no longer bother logging the changes.
Yes, I'm careful with its use but no longer feel I need to be that careful.
Of course, because I do plan my databases carefully (in theory anyway!), its only actually used occasionally within any particular app

However, I regularly reuse objects in new databases & that's when I will often rename tables/queries etc

In my experience over the past 15 years, its totally reliable and doesn't cause any performance issues

But please do try it again e.g. with my test DB but this time leave NAC turned on

 

[ebs17]

However, I regularly reuse objects in new databases & that's when I will often rename tables/queries etc

For something like this, I would use Rick Fisher's Find&Replace or TotalSearch from V-Tools. With these you can perform such a renaming operation in one go, comprehensively and in a controlled manner.
Something like this satisfies my security needs much more.

Set name AutoCorrect options - Microsoft Support

Avoid having your Access desktop database break everytime there is a design changes by using the Name AutoCorrect feature. The Name AutoCorrect can detect and automatically correct problems.

support.microsoft.com

Things that name AutoCorrect does not correct

Name AutoCorrect does not correct the following:
- Changes to form, report, or control names are not tracked.
- Table, query, or field names in macros or in code are not corrected.

Such one-armed giant does not really offer me any benefit.

 

[GPGeorge]

Unfortunately, it's no longer safe to recommend Rick Fisher's tool. It's been really hard to get a response to inquiries or requests for purchase for a couple of years now. It was one of my favorite tools for many years, though. There are others, including Philipp Steifel's Find and Replace, which is particularly good at its job.

 

[Pat Hartman]

Several years later, I went back to it & have used it with ZERO issues for about 15 years.

I didn't say I didn't use it. I explained why it is dangerous if you don't know how it works. If you know how it works, you turn it on and off and that avoids accidents that happen when you rename things and later find everything is now using the "old" version of the query/table and not the new version.

 

[isladogs]

Hi Pat.
I am aware that you use it 'in moderation' and that your approach works for you.
However, my point is that switching it on and off again can actually be the problem due to the risk that changes may not have propagated through.

Unless a developer is scrupulous about checking every single change (which realistically many including myself aren't), I would recommend either leaving it turned on (as I do) or leaving it turned off (as I think @ebs17 is suggesting)

AFAIAC, NAC fulfils its intended purpose exactly as 'stated on the tin'

However, as already mentioned, there are things NAC doesn't do e.g. alter names in code.
I agree 100% about using either V-Tools (free) or Philipp Stiefel's Find and Replace (commercial) both of which do an excellent job.
I also agree with George re Rick Fisher. He no longer seems to be active and does not respond to emails

 

[ebs17]

Another great solution to get to know the database application in front of you:
http://www.accessdependencychecker.com/ from Thomas Koester

 

[isladogs]

Another great solution to get to know the database application in front of you:
http://www.accessdependencychecker.com/ from Thomas Koester

Agreed

 

[Pat Hartman]

Colin, you are quite capable of understanding how the NAC works and avoiding the pitfalls I pointed out. This is not a hill I wish to defend. If you want to recommend that people leave NAC on all the time, go right ahead.

 

[isladogs]

Just to say, prompted by the discussion in this thread I've just published a new web article

Use Name AutoCorrect Successfully

This article explains how the Name AutoCorrect feature works in Access, its limitations and how to use it successfully in your applications

www.isladogs.co.uk

Hopefully I've covered all sides of the discussion in the article. Let me know if I've misrepresented or omitted anything important

 

[ebs17]

A few thoughts on why I virtually never come close to being tempted to want to use NAC.

However, I regularly reuse objects in new databases & that's when I will often rename tables/queries etc

SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField
FROM Table1 INNER JOIN Table2
ON Table1.ID = Table2.ID

- versus-

SELECT T1.ID, T1.DummyTest, T1.NumberText, T2.TextField
FROM Table1 AS T1 INNER JOIN Table2 AS T2
ON T1.ID = T2.ID

If it is the predominantly applied own style to work with table aliases, there is a table name in the query per query level (think of subqueries) exactly once at a defined position => in the FROM part of the query level. Reading the statement quickly, I recognize and distinguish T1 vs. T2 faster and easier than Table1 vs. Table2.
There renaming the table via a separate manual action is very easy, even with so-called complex queries. The latter you only have to be able to read to identify the respective FROM part. I orientate myself on the SQL view of the statement, detached from problems with the handling of the QBE.

In deviation from the recommendation to avoid subqueries as best as possible, my queries often have only tables as data sources to be loaded. So I don't have to worry about intermediate steps like queries that compile partial results, thus I don't have to worry about names of these queries.
This way I have a significant reduction of the number of objects to be considered and can handle the rest by hand.

Tables newly inserted into the project perform new tasks. I don't see any real need to revise the whole project, but only the interfaces (which I should be aware of at this point).
The vast majority of queries are not inimitable highlights of developmental excellence with the need to copy this 1:1 and then adapt it in any processes.
I believe in the imitability, a query is created very quickly, then also suitable and specialized for the concrete operational situation.

 

[isladogs]

I think Google Translate is making your meaning slightly obscure in places so hopefully I've correctly understood what you're saying

If it is the predominantly applied own style to work with table aliases,

Using aliases certainly makes handling any renamed tables simpler / faster. Nevertheless, NAC works perfectly with aliased queries

In deviation from the recommendation to avoid subqueries as best as possible, my queries often have only tables as data sources to be loaded.

As NAC only handles the outermost part of nested queries, it isn't useful (and probably unhelpful) to use it where you have a lot of subqueries.
Having fewer objects to handle isn't particularly important if renaming is only done on rare occasions

The vast majority of queries are not inimitable highlights of developmental excellence with the need to copy this 1:1 and then adapt it in any processes.

!!!???
I reuse several standard objects to many new databases. Mainly a few settings tables, standard forms & code modules.
In fact I store most of the items I use regularly in a template database created for this purpose

Anyway, I'm not trying to argue against your position on the use of NAC
You prefer not to use NAC and I'm not trying to persuade you (or anyone else) otherwise.
My goal was to describe how to use the feature safely without any risk of 'broken queries/forms/reports'

For my own interest I decided to test the performance impact of using NAC.
I modified the example app posted in my web article to add a few more queries including aliased tables & DDL queries
I then looped through each table, query, form & report to measure the total time to open, save & close each object in turn
Doing this ensures all objects affected by the name changes are updated

As expected, using NAC caused a small increase in time. Logging the changes as well increased it still further.
However, the extra time needed was certainly far less than doing all the changes manually.

Obviously the tests are an artificial situation. However, the procedure could be useful as it ensures all name changes are propagated through to all dependant objects (except unsupported objects)

ub 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

DoEvents

    Set db = CurrentDb
 
    Application.Echo False
 
    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
        End If
    Next tdf
 
 
    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

    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

    For Each obj In Application.CurrentProject.AllReports
        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
 
    sngEnd = Timer
 
    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 = 7874 Then Resume Next
   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

I have added the extra info & code to my web article