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.

Synectics

Answer 1

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;

Empressement

Answer 2

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"

Headmost

...using DAO, of course. DAO is the natural interface for controlling Jet, and while DDL can be convenient, it's never been well-supported by Jet/ACE. I've never cared, since I don't alter my schema via code (I do it by hand, since it's something that happens once in a blue moon -- it would take longer to script it and test it than it would take to just do it). – Becket

I found that I had to set the db variable to DBEngine(0)(0) to keep the object open otherwise I was getting an error "Object invalid or no longer set" – Leak

Answer 3

My solution, simple but effective:

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

Pinna

Answer 4

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

Odontoid

Answer 5

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

Ortiz

Answer 6

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.

Hjerpe