SQL to rename a column?

[Jacob Havkrog]

Hi there

Is there some SQL syntax to rename a column in an Access table?

Like

ALTER TABLE MyTable ADD COLUMN MyField

but to the effect that an existing column is renamed. I'd like to avoid
dropping the column and adding it again with a new name.

Thanks
Jacob

 

[Ken Sheridan]

Jacob:

Not in SQL as far as I'm aware, but you can do it with ADO (first be sure
you have a reference to the Microsoft Extensions for DDL and Security object
library in Tools|Refrences on the VBA menu bar):

Public Sub RenameColumn_ADO(strTable As String, strOldName As String,
strNewName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat(strTable)
tbl.Columns(strOldName) = strNewName

End Sub

or DAO (again be sure you have a refernce to the DAO object library):

Public Sub RenameColumn_DAO(strTable As String, strOldName As String,
strNewName As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(strOldName)

fld.Name = strNewName

End Sub

Ken Sheridan
Stafford, England