vbams-access

Change column name of a table


I am trying to change the column name of a table in MS Access using VBA.

Private Sub Change_Col_Name(Table As String, OldColName As String, NewColName As String)
Dim tblDef As TableDef
Dim fldDef As Field

Set tblDef = CurrentDb.TableDefs(Table)
For Each fldDef In tblDef.Fields
    If fldDef.Name = OldColName Then
        fldDef.Name = NewColName
        Exit For
    End If
Next fldDef

tblDef.RefreshLink
CurrentDb.TableDefs.Refresh

End Sub 

I get

error 3420 "Object invalid or no longer set"

for the line

For Each fldDef In tblDef.Fields

Solution

  • Silly me.. This one-liner does it:

    Currentdb.Tabledefs(table).fields(old_col_name).name = new_col_name