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

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


"Jay" wrote:

> 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