Changing Table Names - what's the impact?

[Andy N]

hi there - we have a customer that we run 95 queries for as part of their processing. These queries are based on a number of tables that are named in a specific way. They want to change their main table name, but from what I can see, the table names that the queries are based on, don't always seem to change. Therefore we will need to go through every query potentially to see if it works. Is this right? Thoughts very welcome!

 

[GaP42]

Yes ... assuming that all you have is queries, any change to the name of the main table should require you to run a check to see which queries are impacted.
Using the Object Dependencies tool does not show all queries that depend upon a table: it seems somewhat inconsistent reporting all queries, so it might be best to check either by eyeballing or scanning the collated SQL for the table name - perhaps by gathering the SQL in a report. Of course there are queries that use other queries that may depend upon your table and so do not directly turn up in a simple scan.

 

[cheekybuddha]

You can always just change the main table name, and then create a query which you name the same as the main table's previous name, with simple SQL:

SELECT * FROM NewTableName;

Then you don't have to touch all your existing queries as they will reference the query which in turn references the Main table.

 

[KitaYama]

Not in front of a PC to check the exact naming, but if you tick Track name autocorrect info & Perform name AutoCorrect in options, and you change the name of a table, Access will correct the sql of forms and queries. You don't need to do any manual changes.
Some experts here are against leaving this option ticked all the time. Un-Tick them after you're finishing with your renaming.

@cheekybuddha I assume if someone tries to change the name of a table, it's simply because of giving it a more meaningful name that suggest what it stands for, and be more recognizable for its role in the database, in queries, form record sources etc.
Personally, I don't think your method can help.
What would be the benefits of changing the name of a table then?

 

[tvanstiphout]

@KitaYama: I agree, this is a job for Name Autocorrect. Assuming a split database per best practices, the tables would first be brought in the FE, their relationships re-established, and then change the table name. That will change the related queries as well.
Of course, if you have VBA that composes a SQL statement, you still need to manually fix that. Search the VBA project for the table name.
If you own or buy a tool such as FAR from Access Dev Tools, it can do the Find-and-Replace for you as well.

 

[CJ_London]

Autocorrect will also not correct references to tables in an aliased query e.g.

select *
From (select * from table) as A

table will not be corrected

 

[tvanstiphout]

@CJ: That has apparently been corrected after you last looked at it. In this test I created your query on Companies table, saved it, then renamed the table to CompaniesTest, and the query was fixed up.

 

[Andy N]

What a knowledgeable bunch you are - many thanks for those options. I have thought of another and don't shout at me too much. If we used the existing naming structures but simply imported the new data into a new table, I could send it to then to the old table using a query and then run all the queries with the old names. Not clean but doable??

 

[cheekybuddha]

@tvanstiphout, @KitaYama,

Does Name AutoCorrect work 'after the event'? ie. Does it need to be on before you have made any changes in the first place to know the original names of objects that might be changed, or does it 'know' everything that already exists as soon as you turn it on?

 

[KitaYama]

@tvanstiphout, @KitaYama,

Does Name AutoCorrect work 'after the event'? ie. Does it need to be on before you have made any changes in the first place to know the original names of objects that might be changed, or does it 'know' everything that already exists as soon as you turn it on?

You must tick it On first, then change the name to be able to use Autocorrect.
If the option is not ticked, and you change the name of a table, the queries and form record sources will not be corrected.

 

[KitaYama]

many thanks for those options

There are still more options.
You can write a function, use a loop to go through QueryDefs, read the sql of each query and do the following replace;
Replace Space & OldtableName & Space —> To —> Space & NewTableName & Space
Replace Space & OldtableName & . —> To —> NewTableName & .
Then write back the sql to the query.

 

[KitaYama]

Autocorrect will also not correct references to tables in an aliased query e.g.

select *
From (select * from table) as A

table will not be corrected

I’m not sure about previous versions, but in 365, aliased queries are corrected too.

 

[isladogs]

Not for the first time, I have to disagree with @Pat Hartman about the use of Name AutoCorrect.

Whilst it did cause problems when first introduced in A2000, it definitely can be used safely provided you follow the guidelines in my 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

This is a quote from that article:

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)

 

[KitaYama]

If there were a lot of queries, I would do something like what @KitaYama suggested. I would write a procedure that loops through the querydef collection, opens each query and replaces tblA with tblB, then updates the querydef and moves on.

I just wanted to say there are still other ways too, and really didn't mean to offer it as a solution to OP's question.
This method by itself, doesn't change the sql used for forms' record source. ( SELECT * FROM tblName WHERE False)
It means that you have to also open each form in design view one by one, change their record source too and save them back. Something like following.

To me, it's much of a hustle. I would leave it to Access to do it for me with that Auto correct option.

Sub test()
    Dim sql as string
    Dim i As Integer

    For i = 0 To CurrentProject.AllForms.Count - 1
        DoCmd.OpenForm CurrentProject.AllForms(i).Name, acDesign, , , , acHidden
        sql= Forms(CurrentProject.AllForms(i).Name).RecordSource
        .....' Replace table name, Save and Close it'
    Next

End Sub

 

[GaP42]

Is there not an additional question in the OP? While the above are "solutions" to change the queries after the change of name of the table, there is the further question of assurance that it has worked.
That is, how to test that there have been no unfortunately missed changes - for whatever reason.
You may be very confident of @cheekybuddha 's suggestion at post #3. Other suggestions do involve change to queries. Missing references in other places as @KitaYama suggests with forms shows the risks. Such a change would be incorporated in a "release".
You do need to test as for any change(s) ... you will know from the above procedures the dependencies on the main_table, and check/test the change in the SystemDevelopment (by you) prior to release to a Test (for users to verify) prior to effecting the change within the production db. You would not carry out the change programmatically and not test it. For a release you should have a test plan. I don't think there are shortcuts in that process.

 

[KitaYama]

So, if you have some objects that are only used twice a year, it could be a loooooooong time before they are executed and Access works out that they need to be fixed up and there could be multiple layers of changes in between.

I only reply to the text in bold.
I read somewhere that the Log option bellow autocorrect covers this. No matter how many times object names have been changed, the log finds the path from current name back to previous previous one.
I haven't tested it though.

Edit:
I just tested it. I changed the name of a table and opened several queries. All were OK. I changed the table name again and checked the queries. Again OK. I repeated this step for 15 times and the queries were OK. Then opened a query that I had not opened at all. It means that since the last time the query was opened, 15 times the name of different tables had been changed.
The query opened without any error.
It shows that Access can understand the multiple layer of changes without any difficulty.

If I misunderstood your point, I'd appreciate if you explain it more clearly and if possible with an example.

Thanks.

 

[KitaYama]

Yes, the log covers this UNLESS you have turned off NAC in between. The other place people run into trouble when they leave NAC on is if you rename a table/query, the references follow the new name. So if you rename tblA to tblAold and then recreate tblA, all the references are to tblAold when the change gets propagated.

I understand your point now.
Thanks.

 

[isladogs]

We're agree on the analysis but not on the conclusions
We both agree that after renaming, object dependency information is only propagated when the dependant object is next opened.

That is why I wrote that, 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)

You do a) which is fine providing you are scrupulous at opening all affected objects in turn
I do b) as that will still work correctly even if affected objects are not opened until much later

For several large apps, some objects can have several hundred dependencies. For example:

It really wouldn't be feasible to work through e.g. 126 dependant objects for the Classes table and open each in turn before disabling NAC again
Of course, I almost never rename objects in large, long established mature databases such as that above

It works for me to leave NAC enabled by default. That has been my policy for almost 20 years
I might occasionally turn it off when I need to isolate objects being considered for deletion and even then only after checking depencencies and orphaned objects. However, that is when the problem occurs for any items that have not been opened after being affected by renaming elsewhere.

 

[ebs17]

Things like object name autocorrection and display of sub-data sheets are performance brakes and should be turned off during operation. In my case, they are already turned off in a new, empty database.

 

[isladogs]

Things like object name autocorrection and display of sub-data sheets are performance brakes and should be turned off during operation. In my case, they are already turned off in a new, empty database.

As with many features, there is a balancing act between benefit and performance. For me, NAC offers sufficient benefit, particularly in new databases. Subdatasheets do not, so I disable them.
In the end, it’s a personal decision in these cases.
Progress bars are another example where, in my opinion, providing user feedback outweighs the additional time needed to run a series of tasks.