DDL: Rename Column Help

[tranchemontaigne]

Is there a way to rename a column in VBSQL? I've tried using Oracle/MySQL syntax without luck. Any help with syntax would be appreciated.

ALTER TABLE tblTest
RENAME COLUMN MyMemo TO MyMemoRenamed;

I need to rename a column as part of an update and was hoping to put all of the updates in a code module that could be applied through a function call.

 

[JHB]

Maybe this link may help you.
http://msdn.microsoft.com/en-us/library/office/bb177883(v=office.12).aspx

 

[tranchemontaigne]

I've read the page and see no column rename syntax.

Does this mean that the column cannot be changed through the ALTER TABLE method?

Is there another method I should consider?

 

[gemma-the-husky]

this will rename a column using DAO
there is no error handling in this as yet

Sub changeit()
Dim db As Database
Dim tdf As TableDef
Set db = CurrentDb
Set tdf = db.TableDefs("table1")
tdf.Fields("datafield").Name = "datafieldnew"
End Sub

table1 and datafield need to exist

 

[tranchemontaigne]

Dave,

Excellent post. This is very elegant. I'd add to your reputation on this post if I could, but the forum tells me I need to spread thanks around a bit more before I can thank you again. I haven't spent much time in the meta tables of MS Access.

This is one more reminder that I need to take a better look at DAO model...I've been in MS Access 2K land for the last 14 years - everything I've read from Microsoft has clearly stated that if you are developing for MS Access 2K you really should be employing the ADO model....so I have been using it almost exclusively.

 

[gemma-the-husky]

DAO/ADO - it changed back and forth

I started with A97, and never needed or used ADO.

DAO collections, such as tabledefs, and querydefs are often used in Access, though. eg relinking a back end database is generally done by managing the tabledefs collection, I think.