Change Precision and Scale

[Jay]

Access version: 2007

Is there a way to change the Precision property of a Decimal field using VBA
code? I have tried
myTableDef.Field(FieldNum).Properties("CollatingOrder") = 13
and
myRecordSet.Field(FieldNum).Properties("CollatingOrder") = 13
but both give an "Invalid arguement" error.

I have also tried using an ADO recordset:
rs.Fields("DField").Precision = 13
In this case, no errors are thrown and the property is changed, but when I
close the connection and open the table in design view in Access, the
precision hasn't changed.

I also tried an SQL
ALTER TABLE tablename ALTER COLUMN columname DECIMAL (13,6)
statement, but it gives a syntax error. According to the access help file,
only text and binary fields can take a (size) parameter.

Jay

 

[Jay]

I found a solution using ADO (from another forum):

Dim cs As ADODB.Connection
Set cs = New ADODB.Connection
cs.ConnectionString =
Replace(CurrentProject.Connection.ConnectionString, "BugAndFeatures.accdb",
"testdecimal.accdb")
cs.Open
cs.Execute "ALTER TABLE TestDecimalTable ALTER COLUMN DField DECIMAL
(13,6);"
cs.Close

Jay