Change Field Name from All Caps to Initial Cap

[bo16tx]

Have a database that I'm cleaning up some poor field naming. While in the process, I want to change some all Cap names to initial Cap names, e.g., "COLOR" to "Color". Seems simple, however when I close the database and reopen, the All Caps names remain.

[jwhite]

Where are you changing it? Check that the field name in Table Design is Proper Case. Check that Control Names on the form are Proper Case. If either is not, correct them. Also, if you have DIM'med variables in the top of procedures that are all caps - correct them to Proper Case there.

I refer to "Proper Case" as you noted "Color" as an example. Normally "Camel Case" is used - ItemNumber, DaysToShip, etc.

[isladogs]

Unfortunately Access treats upper and lower case names as the same.
So once you have saved the table, it needs a bit of trickery.

First try, renaming, saving then do a compact and repair.
I doubt it will work but worth trying.

Next, rename the fields e.g COLOR to ColorX. Save
Then rename again to Color. Save again
If my memory is correct, that should work

BUT Color may be a reserved word in Access so it might be wise to rename anyway e.g. ProductColor
If the field is already in use, temporarily turn on Name AutoCorrect before changing names.
That will work with no further messing about.

[ranman256]

sWord = StrConv(sWord, 3)

[orange]

This worked for me (single example) Acc 2010

CurrentDb.TableDefs("tblFatSeq").Fields("FatNo").Name = "FaTnO"

Depending on how many tables/fields are involved, you could set up some looping and use propercase (as ranman suggested) for the strConv option.

Had time to adjust: I had a table with all fields in Upper case-

'---------------------------------------------------------------------------------------
' Procedure : changeCaseOfTableFields
' Author    : mellon
' Date      : 24-Jan-2018
' Purpose   :To change the format of field names in a table
'can be set up with a parameter if needed to do multiple tables
'---------------------------------------------------------------------------------------
'
Sub changeCaseOfTableFields()

          Dim db As DAO.Database
          Dim tdf As DAO.TableDef
          Dim fld As DAO.Field
10       On Error GoTo changeCaseOfTableFields_Error

20        Set db = CurrentDb
30        For Each tdf In db.TableDefs
40            If tdf.name = "datarow" Then
50                For Each fld In tdf.fields
60                    Debug.Print "Initial name :" & fld.name
70                    fld.name = StrConv(fld.name, 3)
80                    Debug.Print "revised name : " & fld.name
90                Next fld
100           End If
110       Next tdf
  
changeCaseOfTableFields_Exit:
120      Exit Sub

changeCaseOfTableFields_Error:
130      MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure changeCaseOfTableFields of Module DataDictionary"
140      Resume changeCaseOfTableFields_Exit
End Sub

Output:
Initial name ELIVERY_HOUR
revised name : Delivery_hour
Initial name ELIVERY_INTERVAL
revised name : Delivery_interval
Initial name :STEM_PRICE_PER_MWH
revised name : Stem_price_per_mwh
Initial name :STEM_QUANTITY_MWH
revised name : Stem_quantity_mwh
Initial name :SUSPENSION
revised name : Suspension
Initial name :TOTAL_BID_QUANTITY
revised name : Total_bid_quantity
Initial name :TOTAL_OFFER_QUANTITY
revised name : Total_offer_quantity
Initial name :TRADE_DATE
revised name : Trade_date