Rename a Table Field Name Problem (1 Viewer)

Eljefegeneo · Apr 19, 2014

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 · Apr 19, 2014

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 · Apr 19, 2014

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 · Apr 19, 2014

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 · Apr 19, 2014

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 · Apr 19, 2014

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 · Apr 19, 2014

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 · Apr 19, 2014

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 · Apr 19, 2014

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 · Apr 19, 2014

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

CJ_London · Apr 19, 2014

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 · Apr 19, 2014

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 · Apr 22, 2014

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 · Apr 22, 2014

gemma-the-husky said:
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 · Apr 22, 2014

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 · Apr 23, 2014

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

Thank You.

RainLover · Apr 26, 2014

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 · Apr 26, 2014

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 · Apr 26, 2014

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 · Apr 27, 2014

Eljefegeneo said:
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.

Rename a Table Field Name Problem (1 Viewer)

Still trying to learn

Super Moderator

Registered User.

Still trying to learn

Still trying to learn

Attachments

Registered User.

Still trying to learn

Super Moderator

Registered User.

Registered User.

Super Moderator

VIP From a land downunder

Super Moderator

VIP From a land downunder

Still trying to learn

VIP From a land downunder

VIP From a land downunder

Super Moderator

Attachments

Still trying to learn

VIP From a land downunder

Similar threads

Users who are viewing this thread