如何使用 Alter Table 在 Access 中创建小数字段? [英] How do I create a decimal field in Access with Alter Table?

问题描述

我想以编程方式在 MS Access 表中创建一个新列.我已经尝试了许多 ALTER TABLE MyTable Add MyField DECIMAL (9,4) NULL; 的排列并得到:

I want to programmatically create a new column in an MS Access table. I've tried many permutations of ALTER TABLE MyTable Add MyField DECIMAL (9,4) NULL; and got:

字段定义中的语法错误

我可以轻松创建一个属于 Double 类型的数字字段，但我想要 decimal.我非常喜欢在单个 ALTER TABLE 语句中执行此操作，而不必创建字段然后更改它.

I can easily create a number field that goes to a Double type, but I want decimal. I would very strongly prefer to do this in a single ALTER TABLE statement and not have to create a field and then alter it.

我使用的是 Access 2003.

I am using Access 2003.

推荐答案

如果要在 acces 表中创建新列，使用 DAO.tableDef 对象很容易:

If you want to create a new column in an acces table, it is easy to use the DAO.tableDef object:

Dim my_tableDef As DAO.TableDef
Dim my_field As DAO.Field

Set my_tableDef = currentDb.TableDefs(my_table)
Set my_Field = my_tableDef.CreateField(my_fieldName, dbDecimal, myFieldSize)
my_Field.decimalPlaces = myDecimalPlaces
my_Field.defaultValue = myDefaultValue

my_tableDef.Fields.Append my_Field

set my_Field = nothing
set my_tableDef = nothing

当然可以进一步删除.

您可能可以使用 ADODB(或 ADOX?)对象这样做，但只要您处理的是 mdb 文件，DAO 就是直接且高效的.

You might have the posibility to do so with ADODB (or ADOX?) object, but as long as you are working on an mdb file, DAO is straight and efficient.

PS:在一些论坛上查看后，十进制字段和DAO似乎存在错误.http://allenbrowne.com/bug-08.html.建议是双倍"(这是我通常做的，以避免与小数舍入相关的任何松散问题)或使用隐式"ADO 来修改您的数据库

PS: after checking on some forums, it seems there is a bug with decimal fields and DAO. http://allenbrowne.com/bug-08.html. Advices are "go for double"(which is what I do usually to avoid any loosy issues related to decimal rounding) or use "implicite" ADO to modify your database

strSql = "ALTER TABLE MyTable ADD COLUMN MyField DECIMAL (28,3);"
CurrentProject.Connection.Execute strSql

这篇关于如何使用 Alter Table 在 Access 中创建小数字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！