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.

 

[Pat Hartman]

Not clean but doable??

Don't do it. use the query technique recommended by @cheekybuddha if you don't want to change all the queries. Once you rename the table, then create a query with the OLD table name that selects the new table. NONE of the other queries will have to change.

Of course WHY the client is in charge of table names is beyond me.

In the long run, actually fixing everything is the best solution. As an old boss reminded us - "if you don't have time to do it right in the first place, what makes you think you have time to do it again?"

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.

Change Auto Correct (or Corrupt as it is sometimes called) is a useful but dangerous "feature". If you understand how it works, you can make it work for you. If you do not understand how it works -- especially that the changes are NOT propagated immediately -- then you will think of the feature as Change Auto Corrupt because the changes will not be applied when/how you think they should be. The changes are not propagated until the next time an object that needs to be changed is opened. 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.

When I do use Name Auto Correct, I always force open all affected objects immediately because I want to turn NAC off so that it doesn't "help" me when I don't want it to and if I didn't force open all objects, the changes I made would never be propagated.

 

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

 

[Pat Hartman]

Whilst it did cause problems when first introduced in A2000, it definitely can be used safely provided you follow the guidelines in my article:

Isn't that exactly what I said? I didn't say the feature didn't work. I only said you need to understand it so that it works for YOU. I even said that I use it.
I've repeated my advice for your convenience.

Change Auto Correct (or Corrupt as it is sometimes called) is a useful but dangerous "feature". If you understand how it works, you can make it work for you. If you do not understand how it works -- especially that the changes are NOT propagated immediately -- then you will think of the feature as Change Auto Corrupt because the changes will not be applied when/how you think they should be. The changes are not propagated until the next time an object that needs to be changed is opened. 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.

When I do use Name Auto Correct, I always force open all affected objects immediately because I want to turn NAC off so that it doesn't "help" me when I don't want it to and if I didn't force open all objects, the changes I made would never be propagated.

 

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

 

[Pat Hartman]

I read somewhere that the Log option bellow autocorrect covers this.

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.

 

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