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

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.

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)

Not many systems need to change field names at runtime.

Why do you actually want to do that?

TC