How do I change a field name in a query without breaking everything else that uses it

[Access9001]

I have a query with a field labeled OldName. I want to rename it to NewName.

However, when I do this, other queries that either have joins/relationships/whatever you call them on OldName break because they can't find OldName after the renaming.

 

[sxschech]

I think access has a track changes option so that if you change a name it changes it elsewhere. I haven't used that though as it slows down the application and may change things when you don't intend to. Instead I use Find And Replace, it is around $40, but it does a lot of things, can find and replace information and names in queries, tables, reports, macros, modules, forms. They offer a free trial. http://www.rickworld.com/

Alternatively, if you know which queries have the name to be changed, you could open them up in SQL view and edit the text of the sql statement to change the name.

 

[Galaxiom]

NameAutoCorrect will track the changes in queries and most of the references in forms and reports. However does not fix references in code or SQL string RecordSources.

Moreover NAC can corrupt objects especially if the new name is already used elswhere in in the database. I have seen it go wrong if a field is given the same name as another table.

V-Tools has a Find And Replace facilty (Deep Search) that handles objects and code. It is a fantastic free suite of tools.

 

[Pat Hartman]

If NameAutoCorrect is all you have, it can do at least some of the work for you but don't forget to turn it off as soon as you've made the change. This "feature" doesn't work the way most people think it should and so they get blindsided by changes it made that it shouldn't.

As has already been mentioned, it ONLY does querydefs, bound reports, and bound forms. It will not find the column in code or in calculate columns. I don't know about macros since I never use them.

Close all database objects.
Turn on NameAutoCorrect
Check the log changes so you can review the changes later if necessary.
Make the change to the column name (you may as well change any others that need changing also because you don't want to do this twice).
Open each query to force the change to propagate. Then do the forms and reports. The propagation doesn't happen automatically. It happens as each object is opened so you want to force open all the objects NOW so you can turn off the "feature" ASAP.