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

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:

Using TableDef.CreateField, then TableDef.Fields.Append

Using a DDL Alter Table ADD COLUMN statement

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.

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

解决方案

You can use DDL to create fields:

Long:

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

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

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:

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