如何在Access with Alter Table中创建一个十进制字段? [英] 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:

字段定义中的语法错误

Syntax Error in Field Definition

我可以轻松地创建一个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

您当然可以进一步删除它.

Of course you can further delete it.

您也许可以使用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

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