Alter Table Rename Column

[ADiecidue]

I don't know if this is the right forum for this, but I am using VB2008 to alter an MSAccess 2003 table. I'm using this code:

"ALTER TABLE Demographics RENAME COLUMN SS# TO SSN;"

I get a "syntax error.." when I run it. I've tried it with single quotes around the field names that doesn't work either. Any help or suggestions would be appreciated.
--Tony--

 

[cjard]

try removing the semicolon ;;;;; also double check that access supports this.. its DDL is very limited. Try running the query directly in access?

 

[Stonkie]

I didn't have to do this yet and I didn't search very long, but I found this which says the keyword "rename" is not part of JET : http://www.issociate.de/board/post/134340/rename_a_column_i_Access.html

And I didn't find anything to disprove it... So you may be simply using a nonexistent function. I don't know for sure myself though and I think there are some way to do it otherwise : http://databases.aspfaq.com/database/how-do-i-rename-a-column.html (the first part is about SQL Server, scroll down a bit for more about Access). It should be possible to use the same ADOX thing (a COM component I think) within .NET .

The easiest way would be to simply do it in Access. If you can't, I guess today's not your day

I'm just giving pointers now, but once you found a solution, could you post it here? I might need that in the near future... And I'd rather it actually be my day then ...

 

[cjard]

you might ahve to create a new column, select the data into it and drop the old one. this does change column order but i never saw why people get so hung up about that (should always use column names)

http://support.microsoft.com/kb/180841

 

[ADiecidue]

How to alter tabel rename column

Thanks to all who responded. I basically gave up using the RENAME key word. While it should work, it just doesn't. I got it to work by transfering the data from the current field to the new field then DROPping the old field.

Gotta love MS!
--Tony--

 

[Stonkie]

Not what I hoped you'd find

 

[cjard]

http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic50261.aspx says jet cannot rename columns without ADOX