What is the DDL to rename a column in MS Access. Something along the lines of "alter table 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.
+1
A:
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;
Mark Wilkins
2010-01-12 13:41:55
+1
A:
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"
Remou
2010-01-12 13:53:32
...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).
David-W-Fenton
2010-01-12 22:47:15
A:
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.
birger
2010-01-12 23:36:45