I'm trying to write a macro to update column headers in a table - any
ideas on how I can do this?

Basically, for a given table I'd like to update column headers e.g. A,
B, C with the values e.g. "Apple", "Boy" and "Cat".

This is related to an earlier problem I listed here** but with a
relaxed approach.

**http://bit.ly/bDFPCQ

Parag wrote:
I don't know why you want to change the field names of a table. That
could really mess up your database.

I created a table called Table1 with 3 text fields; A, B, and C. I then
ran this code. It sets the caption for each field to the value I want.
The end result is A has a caption of "Apple", B is now "Boy", and "C"
is "Cat". Do the same to test it out.

I don't know how you'd do this with a macro unless you did a RunCode in
the macro and ran a similar sub like below.

Public Sub SetTableCaption()
On Error Resume Next

Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Dim prp As Property
Dim strCaption As String

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Table1")

With tdf
'read each field of the table
For Each fld In tdf.Fields
'determine the caption
Select Case Left(fld.Name, 1)
Case "A"
strCaption = "Apple"
Case "B"
strCaption = "Boy"
Case "C"
strCaption = "Cat"
End Select

tdf.Properties("Caption") = strCaption

' If the property didn't exist, there will have been an
' error and the property needs to be added.
If Err.Number = 3270 Then
Err.Clear

Set prp = fld.CreateProperty("Caption", dbText, strCaption)
fld.Properties.Append prp
End If
Next
End With
Set tdf = Nothing
msgbox "Done"
End Sub

On Mar 31, 5:15*am, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Thanks Salad, I'll generate the code for the 4000+ fields. The purpose
of this macro is to clean up the large column headers we're getting
from a clinical data extract. We currently get VISIT/FORM/QUESTION and
need to transform the field to Question (Visit) so it's viewable when
running a query.
Speaking of queries, I've posted another question re querying column
headers here. http://bit.ly/cLydXy