What is the DDL to rename column in MSAccess?

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开发者_C百科 achieve this using ADO.NET in some other way.

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"

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;

My solution, simple but effective:

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

The answer provided by Fionnuala using DDL is

ALTER TABLE [your table] ADD COLUMN [your newcolumn] Text(250)
UPDATE [your table] SET [your table].[newcolumn] = [your table].[old column]
ALTER TABLE [your table] DROP COLUMN [oldcolumn]

Note that obviously you can specify any column type for your new column, and Text(250) is just for illustration

In VBA you can do this to rename a column:

Dim acat As New ADOX.Catalog
Dim atab As ADOX.Table
Dim acol As ADOX.Column
Set acat = New ADOX.Catalog
acat.ActiveConnection = CurrentProject.Connection
Set atab = acat.Tables("yourTable")
For Each acol In atab.Columns
    If StrComp(acol.Name, "oldName", vbTextCompare) = 0 Then
        acol.Name = "newName"
        Exit For
    End If
Next acol

I've looked into this before and there is no DDL statement that can do this for you. Only method is to add a new column, copy the data and remove the old column.