Changing column names in Access 2003 SQL

Question

Changing column names in Access 2003 SQL

leonardmp92 (IS/IT--Management)

(OP)

28 Mar 06 22:21

Hello,

Using Access 2003. I have an existing table and am trying to rename a column to a new name. I know I have to use the ALTER TABLE statement, but then I am not sure how to rename the column. All the references I have show difference "dialects" to rename.

Wondering if anybody can help.

Thank you.

mp9 (Programmer) · Answer 1 · 2006-03-29 03:23:53

I think the only thing you can change with an ALTER COLUMN statement within an ALTER TABLE is data type.

To rename in SQL, I think you'll need to add a new column with tthe name you want, update that column with the value from the old column, then drop the old column.

Maybe you can play with TableDef in VBA to do the renaming?

Golom (Programmer) · Answer 2 · 2006-03-29 07:57:06

As mp9 says, you need to do this in a multi-step process

CODE

ALTER TABLE myTable ADD COLUMN NewColumn Text(255)

UPDATE myTable SET NewColumn = OldColumn

ALTER TABLE myTable DROP COLUMN OldColumn

The RENAME COLUMN functionality isn't supported (as far as I'm aware) in Access.

leonardmp92 (IS/IT--Management) · Answer 3 · 2006-03-29 09:27:17

Thanks guys, but I got a syntax error in the "ALTER TABLE statement" when I tried to run as mp9 listed above.

Come Join Us!

Posting Guidelines

Jobs

Changing column names in Access 2003 SQL