Database Bloat

[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.

 

[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

 

[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