Alter Access DB Precision属性

Question

我正在尝试更改具有decimal数据类型的列的Precision属性。它是用38的精度创建的，但我希望它是10。我如何才能实现它？

Sub MakeDBfile()
Dim daoDB As DAO.Database
Dim daoTable As DAO.TableDef
Dim strSQL As String
Dim FileName as String

FileName = "path to file & file name"

'Creates access file using provided path and file name
Set daoDB = DBEngine.CreateDatabase(FileName, dbLangGeneral)
'Creates table
Set daoTable = daoDB.CreateTableDef("RESP")

'Creates columns and their headers
With daoTable
  .Fields.Append .CreateField("darbskaits", dbDecimal)
End With

'Appends table to access file
daoDB.TableDefs.Append daoTable

strSQL = "ALTER TABLE RESP ALTER COLUMN darbskaits DECIMAL(10,3);"
daoDB.Connection.Execute strSQL

Answer 1

JET和DAO不能很好地支持小数字段。尝试通过ADO/ADODB连接进行连接：

Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection
cn.Execute "ALTER TABLE RESP ALTER COLUMN darbskaits DECIMAL(10,3);"
cn.Close

Answer 2

由于您已经使用DAO创建了字段def，因此您还可以使用它来设置精度，这里以current db为例：

Sub MakeDBfile()
Dim daoDB As DAO.Database
Dim daoTable As DAO.TableDef
Dim f As Field
Dim p As Property

Set daoDB = CurrentDb

Set daoTable = daoDB.CreateTableDef("RESP")

With daoTable
  ' keep field reference (not necessary, can be retreived by name later)
  Set f = .CreateField("darbskaits", dbDecimal)
  .Fields.Append f
End With

daoDB.TableDefs.Append daoTable

' set field property
Set p = f.CreateProperty("Precision", dbByte, 10)
daoDB.TableDefs("RESP").Fields("darbskaits").Properties.Append p