CindyH CindyH - 12 days ago 4
SQL Question

How do I create a decimal field in Access with Alter Table?

I want to programmatically create a new column in an MS Access table. I've tried many permutations of

ALTER TABLE MyTable Add MyField DECIMAL (9,4) NULL;
and got:


Syntax Error in Field Definition


I can easily create a number field that goes to a
Double
type, but I want
decimal
. I would very strongly prefer to do this in a single
ALTER TABLE
statement and not have to create a field and then alter it.

I am using Access 2003.

Answer

If you want to create a new column in an acces table, it is easy to use the DAO.tableDef object:

Dim my_tableDef As DAO.TableDef
Dim my_field As DAO.Field

Set my_tableDef = currentDb.TableDefs(my_table)
Set my_Field = my_tableDef.CreateField(my_fieldName, dbDecimal, myFieldSize)
my_Field.decimalPlaces = myDecimalPlaces
my_Field.defaultValue = myDefaultValue

my_tableDef.Fields.Append my_Field

set my_Field = nothing
set my_tableDef = nothing

Of course you can further delete it.

You might have the posibility to do so with ADODB (or ADOX?) object, but as long as you are working on an mdb file, DAO is straight and efficient.

PS: after checking on some forums, it seems there is a bug with decimal fields and DAO. http://allenbrowne.com/bug-08.html. Advices are "go for double"(which is what I do usually to avoid any loosy issues related to decimal rounding) or use "implicite" ADO to modify your database

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