Renaming Column in VBA

[Eats]

Hi

I am trying to rename a column name in a table using VBA in Access. I have
tried using the RENAME COLUMN sql command, but it doesnt seem to work. Does
anyone know how to rename a table column in Access using VBA?

 

[Alex Dybenko]

In case of DAO - you can use Name propery of field object to rename it
see online help for Name Property

 

[Eats]

Cheers for the help Alex. I finally figured out how to do it. For anyone else
who is interested, here is a function you can use in Access VBA to change the
name of a column in a table. Note the you will have to turn the Microsoft DAO
Object Library on in the References section of VBA:

Public Function Rename_Column(tablename As String, oldcolumn As String,
newcolumn As String)

Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
If tdf.Name = tablename Then
For Each fld In tdf.Fields
If fld.Name = oldcolumn Then
fld.Name = newcolumn
End If
Next
End If
Next

dbs.Close
End Function

 

[Tim Ferguson]

For Each tdf In dbs.TableDefs
If tdf.Name = tablename Then
For Each fld In tdf.Fields
If fld.Name = oldcolumn Then
fld.Name = newcolumn
End If
Next
End If
Next

You don't have to do any iterating: just go directly for the field:-

' protect the whole thing in case the table or the
' field does not exist
On Error Resume Next

' now do the update
dbs.Tabledefs(TableName).Fields(OldColumn).Name = NewColumn

' and pick up the error -- the calling code ought to have
' some idea of what happened...
If err.Number <> 0 Then
' bad, raise an error value
Rename_Column = CVErr(vbObjectError + 2918, "No such Field")

Else
' good, just return the same string
Rename_Column = NewColumn

End If


Hope that helps


Tim F