vba - 使用 VBA 向 MS Access 表添加字段

Question

我需要将计算字段添加到现有表中。我知道有两种方法可以做到这一点，我想知道是否有人对哪种方法最好以及如何使它们起作用有任何意见:

使用 TableDef.CreateField，然后使用 TableDef.Fields.Append

使用 DDL Alter Table ADD COLUMN 语句

我尝试使用第一种方法，但我不断收到 3211 错误，因为 Access 无法锁定表。我没有打开 table 。但是，我正在从已 Access 表中当前存在的字段的表单中调用 CreateField。

下面是调用 CreateField 的代码:

`
Public Sub AddFieldToTable(strTable As String, strField As String, nFieldType As     Integer)

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    On Error GoTo ErrorHandler

    Set db = CurrentDb
    Set tdf = db.TableDefs(strTable)
    Set fld = tdf.CreateField(strField, nFieldType)
    tdf.Fields.Append fld

    MsgBox "The field named [" & strField & "] has been added to table [" & strTable & "]."

    Set tdf = Nothing
    Set db = Nothing

    Exit Sub

    ErrorHandler:
        MsgBox "An error has occurred. Number: " & Err.Number & ", description: " &        Err.Description
        Exit Sub

End Sub
`

我在 tdf.fields.append 行上收到错误消息。执行 ALTER TABLE 语句会更好吗？有哪些权衡？

Answer 1

您可以使用 DDL 创建字段:

长:

CurrentDb.Execute "ALTER TABLE t ADD COLUMN a Long not null", dbFailOnError 

(点击 NOT NULL IDENTITY(1,1) 自动编号)

CurrentDb.Execute "ALTER TABLE t ADD COLUMN b text(100)", dbFailOnError 

bool 值:

CurrentDb.Execute "ALTER TABLE t ADD COLUMN c Bit not null", dbFailOnError 

约会时间:

CurrentDb.Execute "ALTER TABLE t ADD COLUMN d datetime null", dbFailOnError 

备忘录:

CurrentDb.Execute "ALTER TABLE t ADD COLUMN e memo null", dbFailOnError 

显然，这很适合函数化，你可以传入你自己的永恒枚举，结合 Select , 构造字符串并执行它:

Public Sub AddFieldToTable(TableName as string, FieldName as string, _
      FieldType as Long, FieldLen as Long, FieldAllowsNull as Boolean)

Dim FieldText as String

Select Case(FieldType)
    Case 0:
        FieldText = "Long"
    Case 1:
        FieldText = "text(" & FieldLen & ")"
    Case 2:
        FieldText = "bit"
    Case 3:
        FieldText = "datetime"
    Case 4:
        FieldText = "memo"

End Select

Dim Sql as string
Sql = "ALTER TABLE " & TableName & " ADD COLUMN " & FieldName & " " & FieldText

If FieldAllowsNull then
   Sql = Sql & " NULL"
Else
   Sql = Sql & " NOT NULL"
End If

CurrentDb.Execute Sql, dbFailOnError

End Sub