JET SQL question

[leonardmp92]

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.

Mike

 

[office politics]

in access, open the table in design mode, change the column name, and save

 

[leonardmp92]

csamuels...but I want to do it by SQL VIEW, so I need the SQL code.

 

[office politics]

bah fine be difficult

http://www.blueclaw-db.com/alter_table_ddl.htm

Below is the syntax for the alter table statement borrowed from Microsoft Access 2003 help system.

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
ALTER COLUMN field type[(size)] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

ex. ALTER TABLE Employees ALTER COLUMN Emp_Email TEXT(50);

 

[leonardmp92]

Thanks csamuels, great website, but this only provides commands to alter columns by adding, dropping, or changing the field type...not renaming the existing columns. I have searched many websites on the net as well as 3 SQL books I have here on my desk. I cant figure it out.

 

[leonardmp92]

This is what I got below, but I am getting an error: "Syntax error in ALTER TABLE" statement.

ALTER TABLE PURPLE
ALTER COLUMN "COLUMN1" to "COLUMN2", CHAR(15);

Also, after the error statement window is closed, does the part left highlighted show the "location" of the error?

 

[office politics]

looks like you'll have to drop the column and recreate then readd your data

The DDL Alter Table statement provides you with the ability to ADD/DROP table fields and to change data types as well as modify or create indexes.

SQL has a stored procedure called sp_rename but i dont think this will work for an access db