Results 1 to 6 of 6

change field name in a table

This is a discussion on change field name in a table within the Microsoft Access forums, part of the Question Forums category; Access 2010, Windows 7, beginner. How can the name of a field in a table be changed throught the entire ...

  1. #1
    Board Regular
    Join Date
    Jan 2005
    Posts
    418

    Default change field name in a table

    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 by bkelly; Apr 23rd, 2011 at 01:53 PM. Reason: add a phrase
    Thank you,
    Bryan Kelly

    We need to know what a dragon is
    before we study its anatomy.
    (Bryan Kelly, 2010)

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,142

    Default Re: change field name in a table

    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

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Jan 2005
    Posts
    418

    Default Re: change field name in a table

    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.
    Thank you,
    Bryan Kelly

    We need to know what a dragon is
    before we study its anatomy.
    (Bryan Kelly, 2010)

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,142

    Default Re: change field name in a table

    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

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Jan 2005
    Posts
    418

    Default Re: change field name in a table

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

    We need to know what a dragon is
    before we study its anatomy.
    (Bryan Kelly, 2010)

  6. #6
    MrExcel MVP boblarson's Avatar
    Join Date
    Nov 2008
    Location
    Portland, Oregon, USA
    Posts
    1,961

    Default Re: change field name in a table

    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.
    Bob Larson
    Access MVP (2008-2010, 2011)
    Free Access Samples and Tutorials
    (includes Free Frontend Auto Update Enabling Tool).


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com