ALTER TABLE to RENAME COLUMN (Access XP SP2)

Topic

New Reply

I am trying to use the ALTER TABLE command to rename a field in my table and the pertinent code is:
Dim strSQL1 As String
Dim db As DAO.Database
Set db = CurrentDb()
strSQL1 = “ALTER TABLE tblRawData RENAME F1 TO CenterNo;”
db.Execute (strSQL1)
Set db = Nothing

I get a Syntax Error in Alter Table statement message but I can’t figure out what’s wrong with my syntax. Can anyone clue me in?

Reply | Quote

Replies

According to the online help, ALTER TABLE in Jet SQL does not support renaming columns. Neither can you change the Name property of a DAO field object once it has been appended to the Fields collection of a TableDef.
I don’t think there is a way to change a field name programmatically (except by creating a field with the new name, copying all data from the old field to the new one, and finally deleting the old field)

Reply | Quote

According to the online help, ALTER TABLE in Jet SQL does not support renaming columns. Neither can you change the Name property of a DAO field object once it has been appended to the Fields collection of a TableDef.
I don’t think there is a way to change a field name programmatically (except by creating a field with the new name, copying all data from the old field to the new one, and finally deleting the old field)

Reply | Quote

As noted, you cannot use Jet SQL (directly or via DAO) to change a field name once field is appended to a table. To change field name programatically, you’d have to use ADOX Data Definition Language (DDL). Example:

Public Sub RenameFieldADOX(ByRef strTblName As String, _
ByRef strFldName As String, _
ByRef strNewFldName As String)
On Error GoTo Err_Handler

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim strMsg As String

cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTblName)

tbl.Columns(strFldName).Name = strNewFldName

‘ Test Msg:
MsgBox “Field ” & strFldName & ” in Table ” & strTblName & _
” renamed to ” & strNewFldName & ”.”, vbInformation, _
“FIELD RENAMED”

Exit_Sub:
Set cat = Nothing
Set tbl = Nothing
Exit Sub

Err_Handler:
Select Case Err.Number
Case 3265 ‘ Item not found in collection
strMsg = “The specified field or table name was not found.”
Beep
MsgBox strMsg, vbExclamation, “INVALID FIELD OR TABLE NAME”
Resume Exit_Sub
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
Beep
MsgBox strMsg, vbExclamation, “RENAME FIELD ERROR MESSAGE”
Resume Exit_Sub
End Select

End Sub

Example of use with Northwind.mdb:

RenameFieldADOX “Orders”,”EmployeeID”,”WorkerID”

This successfully renamed the EmployeeID field in Orders table to WorkerID. To use this code, you need to set a reference to the ADOX library aka “Microsoft ADO Ext. 2.X for DDL and Security”.

HTH

Reply | Quote

Thanks, Mark, great code! I didn’t know this was possible.

Reply | Quote

Actually, I didn’t know if it was possible either, till I tried it just now. I knew you can’t do it in DAO so decided to try ADO & see what happens….

Reply | Quote

Thank you to both Hans and Mark. I’m going to give Mark’s solution a try.

Reply | Quote

Thank you to both Hans and Mark. I’m going to give Mark’s solution a try.

Reply | Quote

Actually, I didn’t know if it was possible either, till I tried it just now. I knew you can’t do it in DAO so decided to try ADO & see what happens….

Reply | Quote

Thanks, Mark, great code! I didn’t know this was possible.

Reply | Quote