change field name in a table

[bkelly]

Access 2010, Windows 7, beginner.

How can the name of a field in a table be changed throught the entire database? The database is not large, no external references, but this field is referenced in several places.

I tried opening a copy of the mdb file with a binary editor and making a global replace, but that did not work out well. I presume the names are mangled and in some form other than the text seen in the design views.

 

[xenou]

How can the name of a field in a table be changed throught the entire database? The database is not large, no external references, but this field is referenced in several places.

It's probably easiest to just open up the "several places" and make the changes one by one. Generally you just don't want to be changing field names in databases.

Access has an inbuilt means of tracking such changes but it's somewhat inconsistent (it won't work for Append queries, for instance, though it does for Select queries). Allen Browne recommends against using it. I personally haven't found it to cause any problems, but I'm not usually pushing Access too hard.
http://allenbrowne.com/bug-03.html

 

[bkelly]

I have some foreign key fields with the prefix fk_Priority (lower case) and some with PK_Issues (upper case). When I try to change the case and save, Access changes it back.

I tried deleting the relationship but even then I had to delete the field and re-create it.

It seems to me that a global replace throughout the database would be useful.

 

[xenou]

Access probably is case insensitive regarding field names and it's probably best to live and let live as far as that goes (i.e., just let it be). You can try changing it by actually using a different name and then changing it back, with the right casing to see if it "sticks" better:

PK_myField

Change to:
Xpk_myField

Then change to:
pk_myField

 

[bkelly]

xenou,
That intermediate step change you suggested worked out fine.
Thank you.

 

[boblarson]

Just for future reference for anyone finding this thread:

An easy way to rename things is to download and use the free V-Tools and the tool in there called Total Deep Search. An even better one (but costs about $37) is Rick Fisher's Find & Replace. I use that one at work and it is great. In that one you can have it automatically rename but you can have the option to have it pop up a dialog when it finds the name you are wanting renamed and then you can choose whether to let it change it or move on. This can be helpful if you don't want to rename everything named something to something else.