使用 VBA 向 MS Access 表添加字段

Adding field to MS Access Table using VBA

提问by MBY

I need to add a calculated field to an existing table. I am aware of two ways to do this and I'm wondering if anyone has any input on which is best and how to make them work:

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

1. Using TableDef.CreateField, then TableDef.Fields.Append
2. Using a DDL Alter Table ADD COLUMN statement

1. 使用 TableDef.CreateField，然后使用 TableDef.Fields.Append
2. 使用 DDL Alter Table ADD COLUMN 语句

I tried using the first method, but I keep getting a 3211 error because Access could not lock the table. I don't have the table open. However, I am calling CreateField from a form that has accessed which fields currently exist in the table.

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

Here's the code for calling 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
`

I get the error on the tdf.fields.append line. Would executing an ALTER TABLE statement be better? What are the tradeoffs?

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

回答by Lynn Crumbling

You can use DDL to create fields:

您可以使用 DDL 创建字段：

Long:

长：

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

(tack on NOT NULL IDENTITY(1,1)for an autonumber)

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

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

Boolean:

布尔值：

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

DateTime:

约会时间：

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

Memo:

备忘录：

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

Obviously, this lends itself well to functionalization, and you could just pass in your own eternal enum, combined with a Select, to construct the string and execute it:

显然，这非常适合功能化，您可以只传入自己的永恒枚举，结合 aSelect来构造字符串并执行它：

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

回答by MBY

I got the code working with either the CreateField or the ALTER TABLE statement. The key here was that I had used a recordset to access the table's data (I needed to check whether the field already existed and/or contained data before I ran the AddField method). I moved the rst.close statement up to before I edited the table structure and it worked! No more 3211.

我得到了使用 CreateField 或 ALTER TABLE 语句的代码。这里的关键是我使用了一个记录集来访问表的数据（在运行 AddField 方法之前，我需要检查该字段是否已经存在和/或包含数据）。在编辑表结构之前，我将 rst.close 语句移到了上面，并且它起作用了！没有了 3211。

`
Set db = CurrentDb
Set rst = db.OpenRecordset(strTable)

bFieldExists = Field_Exists(rst, strOutputField) ' Custom field_exists in table function

If bFieldExists then nFieldType = rst(strOutputField).Type

If CheckFieldHasValues(strTable, strOutputField) = True Then ' custom CheckField function
    If MsgBox("The output field has values in it. Proceed?", vbYesNo) = vbNo Then Exit Sub
End If

rst.Close ' Recordset must release the table data before we can alter the table!

If bFieldExists = False Then
    AddFieldToTable strTable, strOutputField, dbCurrency
End If

Set db = Nothing

回答by Edgar

I just did the following in a module and it works fine

我只是在一个模块中做了以下工作，它工作正常

Sub AddTableFields()
    Dim db As DAO.Database
    Dim t As DAO.TableDef
    Dim f As DAO.Field
    Set db = CurrentDb
    Set t = db.TableDefs("tl_LongTermStat")

    Dim intY As Integer
    Dim intQ As Integer

    For intY = 2012 To 2018
        For intQ = 1 To 4
            Set f = t.CreateField("Y" & intY & "Q" & intQ, dbText, 10)
            t.Fields.Append f
        Next
    Next
    Debug.Print "AddTableFields() done"
End Sub