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;
Syntax Error in Field Definition
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