ALTER COLUMN in ACCESS

[proteome]

I am trying to use the alter column command in a sql statement to rename a column in an Access table.

DoCmd.RunSQL = "ALTER TABLE theTable ALTER COLUMN F2 TO studentName"

I get an error when running this statement (syntax error) Is the code correct and if so I have been surfing trying to find answers to this problem, can I use the ALTER COLUMN command in ACCESS

 

[Golom]

According to the documentation, the socially approved format for ALTER TABLE is

Syntax

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

There is no "ALTER COLUMN" option. I've seen several posts here where people are attempting to use "ALTER COLUMN" so they must be getting that idea from somewhere ... I just can't find it.

If you want to do things like this you probably need to mess with the field.properties collection using DAO or ADOX. I don't think that there's a pure SQL DDL solution.

 

[proteome]

Actually I made a boo boo I should have written

DoCmd.RunSQL = "ALTER TABLE theTable RENAME COLUMN F2 TO studentName"

I think that I understand what you are saying Access doesn't recognize the Rename Column declaration???

 

[proteome]

Golom,

Is there a way to rename the fields or column names of an Access table using code, I was hoping to use the Alter Table and rename column but I do not think that I can using access. Do you have any suggestions.

 

[TonyJollans]

Hi Golom,

You must have A97, because amongst other things new in Access 2K is the overdue ..

[blue]ALTER TABLE TableName ALTER COLUMN ColumnName ...[/blue]

I don't, however, know of any way to rename columns using Jet DDL, and I agree you ust use the DAO Objects.

proteome,

This will rename a field ..

[blue]

DBEngine(0)(0).TableDefs("[i]TableName[/i]").Fields{"[i]FieldName[/i]").Name = "[i]NewFieldName[/i]"

[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[proteome]

TonyJollans,

Thank you very much!!!!!!!!!!!!!!!!!!!