Create New Field Type=dbDecimal

[Doug Bell]

Hi,

I am getting an error when I try to create a new Field in a Table where the
Field Type is dbDecimal.

I successfully created a Text Field but not sure why this is failing:

With tdfA
.Fields.Append .CreateField(stFieldName, lnFieldType)
End With

Where stFieldName = "WrkStn"
And lnFieldType = dbDecimal (=20)
I am getting an error message 3259 "Invalid field data type"

I am trying to create a Decimal Field with Precision = 6 and Scale=0

Thanks for any assistance,

Doug

 

[Allen Browne]

The Decimal type was new in JET 4, and I don't think Microsoft updated the
DAO library to support it (even though it has been more than 4 years.)

Try using ADOX to Append a Column of type adNumeric to the Table in the
Catalog.

Alternatively, execute a DDL query statement under ADO to CREATE TABLE with
a field DECIMAL (6,0).

You may also need to know that the Decimal field type is implemented so
badly that Access can not even sort it correctly:
http://allenbrowne.com/bug-08.html
After more than 4 years, you have to think that this field is unusable if
they can't be bothered fixing something as basic as that.

 

[Doug Bell]

Thanks Allen,

I jsut couldn't work out why it was erroring.

Microsoft sure do somethings very badly.
I need to maintain continuity moving data through a tiered application
(should not have been Access based but started out as a proof of concept)
and one site has added an additional field and now I need to build a small
utility that will update the other sites to same schema (even though they do
not use that field) without risking data.

I have taken your advice and implementing using ADOX.

Doug