MS Access:以编程方式重命名列

上周我为一个应用程序写了代码:

  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

在本例中,以前的开发人员愚蠢地使用了vba变量命名约定来命名字段名(例如,txtlastname用于应该是lastname的名称),我需要删除所有这些名称。代码的调用方式如下:

  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

您可以很容易地将renameFields()sub修改为对新名称使用replace(),如下所示:

  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

这么说,我真的非常强烈地建议你 不 用空格替换下划线,因为这使得在sql和代码中使用字段名更加困难。我将使用不带下划线、空格或破折号的camel大小写,这样上面代码中的replace()行将变成:

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

将“姓氏”或“姓氏”转换为“姓氏”。字段名可读性的camel大小写一直是access开发人员的标准,只要我在access中编程(从1996年开始),并且避免了由特殊字符或空格引起的问题(即必须用方括号括住字段名)。

你试过用 TableDefs

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

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