How do you rename a field in an Access database using SQL statement?

[RayRayRay]

I want to use an SQL statement to rename a field in an Access 2000 database.
I read about a dozen places on the web that say do it this way:

Alter Table Table1 RENAME Column (Field1) To (Field2)

This does NOT WORK (it chokes on the "RENAME" part). I've also read in about 20 places (including this forum) where this code does not work, so I know it is not me.

Does anyone else know how to do it in a SQL statement, or why this example does not work?

Thanks,

Ray

 

[SonOfEmidec1100]

I dont think you can rename a field with a sql query, the online help does not give any syntax for this.

You can add a new column, set it to to the the original column value then drop the old column, this is not always straight forward if there are constraints or indexes on the field. In this case its sometimes easier to go via a work table then drop the orginal table and recreate it.

 

[bboffin]

I suspect that the only way to do it via SQL is to add a new column with the required name, copy over the data from the old column name using an update query and then drop the old column name.

Example:

ALTER TABLE MyTable ADD COLUMN NewColName ....

UPDATE MyTable Set NewColName = OldColname

ALTER TABLE MyTable DROP COLUMN OldColName

There is definitely no syntax in the SQL language (even for SQL Server) for renaming a column.

Why do you want to do this anyway? The consequences could be disastrous as any code or queries that use that column name will crash once you've done it. Remember that you can give any column an alias in a query simply by adding 'AS NewName' after it in the query.

 

[RayRayRay]

Thanks to both for the responses. I suppose I will have to do it the way you suggested.

It seems there are a lot of people who THINK it can be done, based on my Google searches of the subject. For instance:

http://sqlzoo.net/howto/accesstip557646.htm

Ironically, their sample code fails with the very command they suggest!

Anyway, this will work. As to the question, "why do it?", I'm working on a system that makes infrequent database upgrades in remote locations via SQL statements.

Thanks