change field name in a table

bkelly

Active Member
Joined
Jan 28, 2005
Messages
465
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.
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top