dbDecimal via VBA

[Guest]

I am trying to create a table thru VBA and set one of the fields to numeric
(decimal) with 3 places to the right of the decimal.

I would have thought the following would work but I get an error msg of
"Invalid field data type".

Set DBS = CurrentDB
Set MyTableDefs - DBS.CreateTableDef("TicketInfo")

With MyTableDefs
.Fields.Append .Createfield("Sort_Index", dbDecimal, 3)
End With

DBS.TableDefs.Append MyTableDefs

Can anyone shed some light on how to accomplish this?

 

[Allen Browne]

The code you are using relies on the DAO library, and Microsoft failed to
update that library to cope with the Decimal field type.

That's kind of a blessing really, as JET has no idea how to handle this
field, even in the most basic of queries. Details:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

If you want to create it anyway (not recommended) you can execute a DDL
query statement under ADO, or add a column of type adNumeric using ADOX.

DDL example:
strSql = "ALTER TABLE MyTable ADD COLUMN MyField DECIMAL (28,3);"
CurrentProject.Connection.Execute strSql

Comparison of the names to use in DDL, DAO, ADOX, and JET:
http://allenbrowne.com/ser-49.html