0

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?

*Edits:

-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

Improve this question
3
  • Can you show the string in its final form (i.e. like from the debugger at runtime) and can you indicate what the error is? Commented Aug 18, 2009 at 1:54
  • @JP: I have edited my post to include them. Commented Aug 18, 2009 at 2:06
  • I hope it is not because Access does not allow to change column names...! Commented Aug 18, 2009 at 2:08

2 Answers 2

Reset to default
2

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];
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Thats brilliant! I'll try that out!
Finally, it did not work.. The update table had a syntax error so I had column after set. Then I got another error but this time the error was: The record is too large.
Never mind about the record error... I had added too many columns hahaha! But I still have a syntax error with the update statement you gave me
I think there needs to be a value instead of [test] which is column in the update statement...
I'm not an expert with sql command for Access but this command for MS SQL works: UPDATE mytable SET mycol1 = mycol2 so probably have to tweek the update command a little bit.
|
0

Try ALTER TABLE [Big List] ALTER COLUMN [num] [test] CHARACTER

Improve this answer

5 Comments

The only other thing I can see wrong is perhaps the lack of field size CHARACTER(10) for instance.
Are you folks testing your DDL in Access/Jet/ACE before posting it? If not, you really are wasting your time. Every db engine has its own SQL dialects, and Jet/ACE has always been a latecomer to SQL compatibility (it was created in 1991 and SQL was not introduced to the engine until the following year, and it was a dialect of SQL 89, because SQL 92 was not even accepted yet).
@David W. Fenton: Your history is wrong. ANSI-92 Query Mode was introduced into Jet 4.0 circa 1999 but the SQL Server team was prevented from making it compliant with the SQL-92 standard by the Windows team because doing so would break their apps. The usual MS politics (rolls eyes).
Renaming a column is outside the scope of the SQL-92 standard. The only alter column actions SQL-92 defines is <set column default clause> and <drop column default clause> and the Access database engine fully supports the SQL-92 syntax here. If a SQL DDL syntax provides means to rename a column then these will be vendor extensions and SQL-92 will not apply.
Yes, I ran mine in my copy of Access before posting, but I'm not on the same version as he is, and depending on details, he could be calling a different engine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.