How to automate renaming field names

[ppataki]

Dear All,

Does anybody know how to rename field names with a help of a macro or with VBA?

How I do that at the moment is that I created another table with the correct field names and I append the data with a query
It is quite a good solution but there is huge data movement (approx 30 000 lines), so it would be better somehow to rename the fields only

Many thanks in advance

 

[pbaldy]

I doubt it can be done in a macro. In code you could probably use a TableDef. More info in VBA or DAO help.

 

[Julian]

Try, the following, but beware how this interacts with relationships and keys.

Sub TableColumnAlter(Table As String, oldName As String, newName As String)
Dim counter1 As Long
Dim counter2 As Long
Dim tbl As TableDef
Dim fld As Field
Debug.Print CurrentDb.TableDefs.Count
For Each tbl In CurrentDb.TableDefs
If tbl.Name = Table Then
For Each fld In tbl.Fields
If fld.Name = oldName Then
fld.Name = newName
Exit For
End If
Next
Exit For
End If
Next

End Sub

 

[ppataki]

Thank you very much for the solution!!