SQL 如何使用 Alter Table 在 Access 中创建小数字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/180929/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create a decimal field in Access with Alter Table?
提问by CindyH
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:
我想以编程方式在 MS Access 表中创建一个新列。我尝试了许多排列ALTER TABLE MyTable Add MyField DECIMAL (9,4) NULL;并得到：
Syntax Error in Field Definition
字段定义中的语法错误
I can easily create a number field that goes to a Doubletype, but I want decimal. I would very strongly prefer to do this in a single ALTER TABLEstatement and not have to create a field and then alter it.
我可以轻松创建一个Double类型的数字字段，但我想要decimal. 我非常愿意在单个ALTER TABLE语句中执行此操作，而不必创建一个字段然后更改它。
I am using Access 2003.
我正在使用 Access 2003。
采纳答案by Philippe Grondier
If you want to create a new column in an acces table, it is easy to use the DAO.tableDef object:
如果要在 acces 表中创建新列，使用 DAO.tableDef 对象很容易：
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.
当然你可以进一步删除它。
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.
您可能有可能使用 ADODB（或 ADOX？）对象这样做，但只要您处理的是 mdb 文件，DAO 就是直接且高效的。
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
PS：在一些论坛上查看后，似乎有十进制字段和DAO的错误。http://allenbrowne.com/bug-08.html。建议是“加倍”（这是我通常做的，以避免与小数舍入相关的任何松散问题）或使用“隐式”ADO 来修改您的数据库
strSql = "ALTER TABLE MyTable ADD COLUMN MyField DECIMAL (28,3);"
CurrentProject.Connection.Execute strSql
回答by Chris OC
The decimal data type isn't supported in the default Jet 4.0 mdb file. You have to use the SQL Server compatibility syntax (ANSI 92) setting to use the decimal data type in the SQL Window.
默认的 Jet 4.0 mdb 文件不支持十进制数据类型。您必须使用 SQL Server 兼容性语法 (ANSI 92) 设置才能在 SQL 窗口中使用十进制数据类型。
Click on the menu, Tools > Options. Click on the Tables/Query tab. Mark the check box for "This database" in the SQL Server compatibility syntax (ANSI 92) section. This mode will affect the entire db, including queries with wildcards, so you may want to try this on a copy of your db.
单击菜单，工具 > 选项。单击表/查询选项卡。在 SQL Server 兼容性语法 (ANSI 92) 部分中选中“此数据库”复选框。这种模式会影响整个数据库，包括使用通配符的查询，所以你可能想在你的数据库副本上试试这个。
Paste this into the SQL window:
将其粘贴到 SQL 窗口中：
ALTER TABLE MyTable
Add COLUMN MyField DECIMAL (9,4) NULL;
If you don't want to alter the mode of your database, you must use vba code with the adodb library:
如果您不想更改数据库的模式，则必须将 vba 代码与 adodb 库一起使用：
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
conn.Execute "ALTER TABLE MyTable " _
& "ADD COLUMN MyField DECIMAL (9,4) NULL;"
conn.Close
回答by Mitch Wheat
The ALTER TABLE syntax is only supported in Jet 4.0/ACE while in ANSI-92 Query Mode. Try an ADO connection e.g.
只有 Jet 4.0/ACE 在 ANSI-92 查询模式下才支持 ALTER TABLE 语法。尝试 ADO 连接，例如
CurrentProject.Connection.Execute "ALTER TABLE myTbl ADD COLUMN myColumn DECIMAL(9,4)"
CurrentProject.Connection.Execute "ALTER TABLE myTbl ADD COLUMN myColumn DECIMAL(9,4)"
