使用VBA将字段添加到MS Access表 [英] Adding field to MS Access Table using VBA
问题描述
我需要将一个计算字段添加到现有表中.我知道有两种方法可以做到这一点,并且我想知道是否有人对最好的方法以及如何使它们发挥作用有任何投入:
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:
- 使用TableDef.CreateField,然后使用TableDef.Fields.Append
- 使用DDL Alter Table ADD COLUMN语句
我尝试使用第一种方法,但是由于Access无法锁定表,所以我不断收到3211错误.我没有桌子开着.但是,我从已访问表中当前存在哪些字段的表单中调用CreateField.
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.
这是调用CreateField的代码:
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
`
我在tdf.fields.append行上收到错误.执行ALTER TABLE语句会更好吗?权衡是什么?
I get the error on the tdf.fields.append line. Would executing an ALTER TABLE statement be better? What are the tradeoffs?
推荐答案
您可以使用DDL创建字段:
You can use DDL to create fields:
长:
CurrentDb.Execute "ALTER TABLE t ADD COLUMN a Long not null", dbFailOnError
(在NOT NULL IDENTITY(1,1)
上粘贴一个自动编号)
(tack on NOT NULL IDENTITY(1,1)
for an autonumber)
CurrentDb.Execute "ALTER TABLE t ADD COLUMN b text(100)", dbFailOnError
布尔值:
CurrentDb.Execute "ALTER TABLE t ADD COLUMN c Bit not null", dbFailOnError
DateTime:
CurrentDb.Execute "ALTER TABLE t ADD COLUMN d datetime null", dbFailOnError
备注:
CurrentDb.Execute "ALTER TABLE t ADD COLUMN e memo null", dbFailOnError
显然,这很容易实现功能化,您可以传入自己的永恒枚举,并结合Select
来构造字符串并执行它:
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
这篇关于使用VBA将字段添加到MS Access表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!