SQL: Rename Field ? (ADO or DAO)?

[Guest_imported]

I'd like to know how to rename a field. My code below doesn't work at all.

Set objRS=Server.CreateObject("ADODB.Recordset&quot
strQuery="ALTER TABLE " &TableName & " RENAME " & FieldName & " TO " & FieldNewName
objRS.Open strQuery, objConn

I have the error:
Microsoft JET Database Engine error '80040e14'
Syntax error in ALTER TABLE statement.



We told me on a forum to try DAO as ADO doesn't support ALTER TABLE:

Dim dbs As Database
Set dbs = OpenDatabase(database)
dbs.Execute "ALTER TABLE " & tablename & " RENAME COLUMN " & Title & " to " & newTitle
dbs.Close

But it doesn't work, I have this error:

Expected end of statement
/db/dev/SaveChangeSpec.asp, line 51
Dim dbs As Database
--------^

Maybe I must think about another connection?

 

[AWithers]

Whether the commands works depends to some extent on the underlying data and maybe in the case there is a problem. A good idea is to capture the sql string build just before it executes (write it to the screen) and then use this in your database and see if it works.

Instead of renaming the field can you not just use an alias in your query

e.g.
select FieldName as FieldNewName from TableName

Hope this helps

Andy