change field names through code

[ChapcoRyno]

Is there a way to change field names using the Alter
Table command or something similar so that my code could
automatically change field names to what I specify
without losing the data in the table? Any help would be
appreciated, thanks in advance!

 

[Danny J. Lesandrini]

This code works for me, but it's not an ALTER query ...


Function ChangeFoo()
On Error Resume Next

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim i As Integer

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

For Each fld In tdf.Fields
i = i + 1
fld.Name = "Field_0" & i
Next

Set tdf = Nothing
Set dbs = Nothing

End Function

 

[Scott McDaniel]

I don't think you can rename a column through SQL on a Jet table ... you can
add the new column, copy the data from old to new, then drop the old column
.... you can change the datatype and size of the column, but AFAIK that's
all. And, this only goes for A2K and up ... if your using 97, you'll have to
resort to Danny's suggestion or the 3 step process above.

 

[Greg Kraushaar]

It is probably safer to set the Caption Property
(assuming DAO)
set tdf = db=tabledefs("YourTable")
tdf.Fields("MyField").Caption = "Fred"

You will need to trap for the case where the Caption has never been
set, or else go through and manually set all the captions the first
time.

Changing field names on the fly is fraught with difficulty. You not
only need to change the field name, you also need to catch and fix
every reference to it. (Forms, Queries, OLEControls, reports and so
on)

I am in the process of fixing some one elses DB.
One of the problems (I wish it was the only one!) is that he has
hardcoded Field names like "Sep 2003" in tables & this is expected to
work next year!

If you are doing this to display data on a report, consider setting a
label on a report directly. You can change the label in the OnOpen
event

Other options,
Use a temporary table, delete it each time, and use a make table
(I personally hardly ever do this)
Create a query to dynamically set the field name for the query
(SELECT Month1 as Jul from tblQuarterData)

 

[TC]

Not many systems need to change field names at runtime.

Why do you actually want to do that?

TC