I am having trouble with a ALTER TABLE command that I am trying to use on a MS Access database in a C# project. I am trying to rename a column and change the type at the same time.
Here is my command:
string sqlCommand = "ALTER TABLE " + tableName + " CHANGE [" + oldColName + "] ["
+ newColName + "] " + colType;
What is wrong in this command and what do I need to do to make this it work?
-The type and the names of the table, new column and old column are not the problem!
-The exception that is catched is :
Syntax error in ALTER TABLE statement.
-The final string looks like this:
ALTER TABLE [Big List] CHANGE [num] [test] CHARACTER
-Connection provider:
Microsoft.ACE.OLEDB.12.0
I don't think you can rename a column with SQL and access.
The best way to achieve this is to create a new column with the new name, update the new column and drop the old one.
ALTER TABLE [Big List] ADD COLUMN [num] YOURTYPE;
UPDATE [Big List] SET [num] = [test];
ALTER TABLE [Big List] DROP COLUMN [test];