Rename a Table Field Name Problem

[Eljefegeneo]

I must be going crazy, but yesterday I was working on my DB, cleaning up some of the field name that were cumbersome and everything worked fine. Today I was doing some follow up work and I cannot change the field names. All I want to do it change the name of a field from CITY to City, but it won't let me do it. I have tried some of the suggestions that I have found on various forums, change from single click to double click, but I was already using double click to open an object; unchecked Auto-Correct, still nothing.

Yesterday I was able to modify a name from Actions to Action (simply deleting one letter. And State/Province to StateProvince. Worked fine.

What bothers me is that I have been able to change a field name before and now I can't. I have tried compact and restore, etc. What am I doing wrong?

 

[CJ_London]

I was doing some follow up work and I cannot change the field names.

why not - do you get an error message? are you able to change the name, but then not able to save the changes or what?

Are others using the db at the same time as youa re trying to change something?

 

[Fran Lombard]

Changing names of fields is always risky business - you will need to make sure any object (form, query, macro, report, vba code ...) that references that field is updated to the new name.

Are you getting error messages? Are the fields used in any relationships? Im pretty sure you can't change a field name if its used in a relationship. You would have to delete the relationship, make the name change then re-establish the relationship.

 

[Eljefegeneo]

No, I am the only user, the table does not have any relationships, no forms, no vba, queries, nothing. The table is merely a repository for names that were deleted from the main table of names. That is why it is so confusing. I will post a sample of my problem.

 

[Eljefegeneo]

The attached Db contains the table which is giving me problems. When I try to change the field name of SURNAME to Surname, it seems to do it, but when I close the table and reopen it, it the field name is SURNAME again. Likewise for the other field names. However, if I type SURNAME1, it allows me to change the name. Then I re-type it as Surname, all is OK. That is what happened in the copy of the table.

I am sure it is some simple thing that I am missing, but it is very confusing. Thanks for any help that you can give me on this.

 

[Fran Lombard]

Sounds like a simple case of case insensitivity. Since your attempted change to the name was only from upper to lower case access was not recognizing any change. So there was no real change to save. But when you changed the name by adding an extra character then removing the extra character while changing the case a real change was made and then the new name was saved.

 

[Eljefegeneo]

Yes, but if I change the name from SURNAME to Surname, is there a problem with the naming of the field or control in a form? That is, will any VBA or macros be affected?

 

[CJ_London]

Interesting

I'm able to duplicate the issue in other db's so I don't think it is you. The only workaround I found was similar to yours, in my case I deleted the characters I wanted to change case, clicked the save button then retyped and saved.

I'm inclined to agree with Fran except that if you just change the case and close the design window, Access says 'design has changed, do you want to save'

 

[Fran Lombard]

No shouldn't have any problem. As you have stated you have no forms, vba macros or other objects currently referencing the field so you didn't break anything by changing the name of the field. Any new objects you create will reference the new field name.

My comment regarding changing field names is risky business was referring to the requirements to update existing objects to the new name.

For instanced, if you had a report that was using a field and you change the name of the field in the table - the report would not run until you updated the report to reference the new name. You should be all set.

 

[Fran Lombard]

I just tried to recreate the issue in Access 2007 and could not. I wonder if different versions are treating case differently.

 

[CJ_London]

I'm using access 2010 and just tried changing an MDB (from 2010) and the problem occurs there as well.

It may have something to do with autocorrect, but I have that turned off.

Not a problem I've come accross before, If I want to 'keep' a deleted field I just prefix it with 'zz' - so I've changed the name anyway!

 

[RainLover]

Eljefegeneo

The situation here is that you are using 2010 and not too many people have that. In order to be able to look at it one must have 2010 which they don't.

In order to look closer can you convert back to Access 2003.

From what you have said there is no reason why you should not be able to change the field. I would question why you would want to change a field name on a regular basis. If this is the case then I would advise against it. No one should be playing with the back end and it should not be accessible

In order to overcome the current problem I would suggest that you open the Database with the shift key held down. Now that the database is open change the name of the field in the table from the Database Window. I can see no reason why this should not work.

If this does not work and as a last resort you could use a Caption against the table name.

One other thing. If this is a split data base then the change must be made in the back end.

 

[gemma-the-husky]

I think because access is cade insensitive, by changing the field name from CITY to City - there is no actual change. If you change it to some other name, and then change it again, it will probably be OK.

I have seen this effect in some other code changes as well. Setting event properties for forms, I think.

 

[RainLover]

I think because access is cade insensitive, by changing the field name from CITY to City - there is no actual change. If you change it to some other name, and then change it again, it will probably be OK.

I have seen this effect in some other code changes as well. Setting event properties for forms, I think.

I always thought City to be different to CITY. So I tested it and never had a problem. I could change the Field to anything I wanted without problem. I used 2003.

I wonder what happened to the OP. It has been several days since we got a proper reply.

 

[Eljefegeneo]

Thank you. I think the "problem" is that Access 2010 doesn't care if the field name is City or CITY. Thus it doesn't see any change and therefore makes no change.
All I was initially trying to do it clean up a DB which was way too long in developing due to the problem of transferring data from an old DOS based DB called DataEase into an Access DB. I didn't want any future user to try and figure out why some fields were in all caps and some not.

The "problem" has been, therefore solved, by changing the name to CITY1 and then changing it again to City.

Thanks to all who gave me more than their two cents (or pence) worth.

 

[RainLover]

It is good that you have supplied the final outcome so that all can learn.

Thank You.

 

[RainLover]

I have had another look at this problem and am not 100% sure of the correct answer.

Could you please post a faulty Table so that I (and others) can have a proper look.

I need and so do others, this version to be in Office 2003. If you can supply that then that would be really good.

Thanks

 

[CJ_London]

The problem seems to be specific to 2010 (and 2013 anybody?), Fran Lombard says he doesn't get it in 2007. I certainly get it in 2010 - any table, any db will demo the problem.

If I open a .mdb in 2010 I also get the problem - see attached video. I've also attached the db which is dbRotate from Stephen Lebans so will have been created in Access 2003 or earlier

 

[Eljefegeneo]

I think that the post from CJ London illustrates the problem. Whether or not one might consider this an Access 'bug" or not, it does seem to have caused some concern. It must have something to do with the case sensitivity or rather the lack thereof of it in naming fields. I hope my posting will at least show others that this is not really a problem.

I did try and change the "autocorrect" feature, but this had no affect on the outcome.

So, in essence, the problem or rather the concern, is solved.

 

[RainLover]

I think that the post from CJ London illustrates the problem. So, in essence, the problem or rather the concern, is solved.

The problem is not really solved.

There is no problem if you save after making the change.

The Video shows too little to be of use.