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
Silly me.. This one-liner does it:
Currentdb.Tabledefs(table).fields(old_col_name).name = new_col_name