What is the DDL to rename column in MSAccess?

Question

What is the DDL to rename a column in MS Access? Something along the lines of:

alter table myTable rename col1 to col2

which does not work for MSAccess 2000 format databases. I'm using OLEDB or ADO.NET with a MSAccess 2000 format db but would be grateful of any hint at the syntax or a suggestion as to how to achieve this using ADO.NET in some other way.

Answer 1

I do not believe you can do this, other than by appending a new column, updating from the existing column and then deleting the 'old' column.

It is, however, quite simple in VBA:

Set db = CurrentDb
Set fld = db.TableDefs("Table1").Fields("Field1")
fld.Name = "NewName"

Answer 2

I am at home and can't test this at the moment, but I think this should work. This site has information about it.

ALTER TABLE thetable ALTER COLUMN fieldname fieldtype

Edit I tested this a bit and oddly enough, you can't rename a column that I can find. The ALTER COLUMN syntax only allows for changing type. Using SQL, it seems to be necessary to drop the column and then add it back in. I suppose the data could be saved in a temporary table.

alter table test drop column i;
alter table test add column j integer;

Answer 3

My solution, simple but effective:

Dim tbl as tabledef
set tbl = currentdb.TableDefs("myTable")
tbl.fields("OldName").name = "Newname"