VBA - change column name function

[Kingz]

Hi,

I wrote this little function as part of an ETL procedure. It's simply to help me change column names in my access table, in VBA..

Public Sub change_col_name(TableName As String, OldColName As String, NewColName As String)
Dim tblDef As TableDef
Dim fldDef As Field

Set tblDef = CurrentDb.TableDefs(TableName)

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

For some reason I get an error 3420 on the "For each" line..

I'm grateful for any advice..

 

[Uncle Gizmo]

Try:-

Public Sub change_col_name(TableName As String, OldColName As String, NewColName As String)
    Dim sql As String

    ' Build the SQL statement
    sql = "ALTER TABLE [" & TableName & "] RENAME COLUMN [" & OldColName & "] TO [" & NewColName & "]"

    ' Execute the SQL statement
    CurrentDb.Execute sql, dbFailOnError
End Sub

 

[Kingz]

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

Does it

 

[Eugene-LS]

I get an error 3420 on the "For each" line..

Try:

Public Sub change_col_nameOld(TableName As String, OldColName As String, NewColName As String)
Dim tblDef As DAO.TableDef
Dim dbs As DAO.Database
Dim iVal As Integer
    
    Set dbs = CurrentDb
    Set tblDef = dbs.TableDefs(TableName)

    For iVal = 0 To tblDef.Fields.Count - 1
        If tblDef.Fields(iVal).Name = OldColName Then
            tblDef.Fields(iVal).Name = NewColName
            Exit For
        End If
    Next iVal

'    tblDef.RefreshLink
'    CurrentDb.TableDefs.Refresh

    Set tblDef = Nothing
    Set dbs = Nothing

End Sub

Shorter way:

    CurrentDb.TableDefs(TableName).Fields(OldColName).Name = NewColName