What happens if I change a table field name?

[tatarik]

Hi people!


If I manually change the name of a table fields, will all the other references to this fields (in queries, forms, controls, procedures) be updated accordingly to reflect the name change?

T.

 

[John Big Booty]

No, you will need to change all references to that field name to reflect the changes you make.

 

[Galaxiom]

There is rarely a good reason to change fieldnames because they are never really exposed to the user in a good design. You can change label captions on forms and reports instead. It is rarely worth the effort unless it is very early in the project.

If NameAutoCorrect is enabled, references to the fields in Queries, Forms and Reports will be changed the next time they are opened.

References in VBA code, SQL strings in the RecordSource and RowSource properties won't be changed automatically.

However occasionally NameAutoCorrect can cause corruption that can be hard to fix. You will need to thoroughly test everything, and I mean EVERYTHING in the project.

If you really want to go ahead then download V-tools and use its Total Deep Search to find all references to the fieldname in the project.

 

[tatarik]

Galaxiom,

Thanks for your GREAT answer.

As you suggested that NameAutoCorrect might cause corruption, I'll give a try to V-tools.

Thanks to both of you as I was about to change a table field name and I now realize that it's also not always necessary as the end user doesn't have to see them

Have a great day,
T.

 

[Galaxiom]

Note that Name Auto Correct is on by default.

If you haven't already, do remember to turn it off in Access options before you change anything.

 

[tatarik]

So for a serious Access user, would it be advised to turn the NameAutoCorrect feature off?

 

[Galaxiom]

It is a personal choice. Many serious developers avoid it. I try to avoid changing object names but when I do I let NAC do the job. It is usually very early in the design if I do it and before a lot of code has been done.

I take a few minutes to consider how much the change is likely to be propogated before deciding.

I have personally experienced corruption after renaming with NAC on. Many developers abandon it as soon as they get the first problem but I persisted and found what had happened.

It appears that NAC can sometimes confuse different objects that have the same name. If I remember correctly in my case it was something to do with an existing field that happened to have the same name as the new name I gave to a different table. Since then I stay aware to avoid such naming conflicts at all times regardless.

The symptoms don't give much of a clue to what could be wrong.

I once renamed many things in an early database design to remove reserved words. It took hours to get it all working properly again.