MS Access: programmatically rename columns

Question

I need to update all column names in an Access database of 8 tables. In short, each underscore in a column name should be replaced with a space. Can you recommend a fast method for doing this?

Answer 1

Did you try using TableDefs

DatabaseName.TableDefs("TableName").Fields("FieldName").Name = "NewName"

http://msdn.microsoft.com/en-us/library/bb220949.aspx

Answer 2

I wrote code for this for an app just last week:

  Public Sub RenameFields(ByRef tdf As DAO.TableDef)
    Dim fld As DAO.Field
    Dim strFieldName As String
    Dim strNewName As String

    Debug.Print "==============================================" & vbCrLf & UCase(tdf.Name)
    For Each fld In tdf.Fields
      strFieldName = fld.Name
      strNewName = Mid(strFieldName, 4)
      Select Case Left(strFieldName, 3)
        Case "boo", "byt", "cur", "dat", "hyp", "int", "mem", "obj", "txt"
          fld.Name = strNewName
          Debug.Print tdf.Name & "." & strFieldName & "=>" & strNewName
      End Select
    Next fld
    Set fld = Nothing
  End Sub

In this case, a previous developer had stupidly used VBA variable naming conventions for the field names (txtLastName for what should be LastName, for instance) and I needed to remove all of them. The code is called like this:

  Dim tdf As DAO.TableDef

  For Each tdf in CurrentDB.TableDefs
    If Left(tdf.Name,3) = "tbl" Then ' <= this line may differ for you
       Call RenameFields(tdf)
    End If
  Next tdf
  Set tdf = Nothing

You could easily adapt the RenameFields() sub to use Replace() for the new name, something like this:

  Public Sub RenameFields(ByRef tdf As DAO.TableDef)
    Dim fld As DAO.Field
    Dim strFieldName As String
    Dim strNewName As String

    Debug.Print "==============================================" & vbCrLf & UCase(tdf.Name)
    For Each fld In tdf.Fields
      strFieldName = fld.Name
      strNewName = Replace(strFieldName, "_"," ")
      If strFieldName <> strNewName Then
         fld.Name = strNewName
         Debug.Print tdf.Name & "." & strFieldName & "=>" & strNewName
      End If
    Next fld
    Set fld = Nothing
  End Sub

All that said, I'd really, really strongly suggest that you not replace the underscores with spaces, as this makes it much, much more difficult to work with the field names in SQL and in code. I would use CAMEL case with no underscores or spaces or dashes so that the Replace() line in the code above would become:

  strNewName = Replace(StrConv(Replace(strFieldName, "_"," "), vbProper)," ", vbNullString)

That would convert "Last_Name" or "last_name" to "LastName". Camel case for readability of field names has been standard for Access developers as long as I've been programming in Access (since 1996), and avoids the issues caused by special characters or spaces (i.e., having to surround the field name with square brackets).