Renaming columns

[micang]

Access 2003

Hi All,

What is the best way of renaming 60 columns in a Access table?

Is there another option other than in design view and retyping the headings?

Many thanks.

Michael

 

[LarryDeLaruelle]

Have you tried the caption property in table design?

Larry De Laruelle

 

[lespaul]

First, tables have fields (not columns) and records (as sometimes referred to in this context as rows). Now, you want to rename 60 fields in the same table? Please tell me that you don't have:
[tt]
SomeField1 SomeField2 SomeField3 .... SomeField60
[/tt]

and you want to do:
[tt]
NewName1 NewName2 NewName3 ...... NewName60[/tt]

If you do have a structure that conforms to this naming convention, you're tables are not normalized and you should read the fundamentals document linked below.

If you have no choice and can't fix the table structure to a normalized database, I'll try and search for a similar posting and supply a thread link for you.

HTH



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

First, tables have fields (not columns)
Only in Micro$oft world, I guess ;-)

 

[lespaul]

have a look at Thread181-1075467

[off topic]well you know how Micro$oft thinks that the world revolves around them!! Hopefully we can do our best to educate the users so we can disabuse Micro$oft of this notion![/off topic]

 

[PHV]

[off topic (continued)]Fortunately the DDL implementation in JetSQL still play with COLUMN (not FIELD) ! [/off topic]

 

[micang]

Hi All,

Thanks for the replies.

Yes, all I want to do is rename the field headings. I inherited a database and all the headings have spaces e,g Dvla Code. I want to rename it to Dvla_Code or even DvlaCode.

I just thought there could be a quick way of doing it.

Many thanks

Michael

 

[AlexCuse]

Hehe, leslie that was the anti-Celko answer

Ignorance of certain subjects is a great part of wisdom

 

[Remou]

micanguk
How about:

Dim tdf As TableDef
Dim db As Database
Set db = CurrentDb

For Each fld In db.TableDefs("tblA").Fields
    fld.Name = Replace(fld.Name, " ", "_")
Next

 

[lespaul]

Remou,
that looks suspiciously like the answer you gave in the thread I linked!

[off topic]OK, I have to admit I don't get Alex's joke. I googled Celko and discovered he's a database JUNKIE, but I don't see how my response above is anti-Celko; please excuse my ignorance and explain it to me (I know, explaining it makes it not funny anymore, but I'd still like to get it![/off topic]

Les

 

[Remou]

lespaul,
It is, but with the addition of looping through the fields. The link was to a thread I had replied to, so I thought it would be ok to suggest a change that applied to micanguk. Perhaps you intended to post a different link?

 

[micang]

Thanks all, I am going to give the code above a go.

Michael