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

Question

I want to programmatically create a new column in an Access table. I've tried many permutations of "ALTER TABLE MyTable Add MyField DECIMAL (9,4) NULL;" and gotten "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. Thanks! I am using Access 2003.

Answer 1

The ALTER TABLE syntax is only supported in Jet 4.0/ACE while in ANSI-92 Query Mode. Try an ADO connection e.g.

CurrentProject.Connection.Execute "ALTER TABLE myTbl ADD COLUMN myColumn DECIMAL(9,4)"

Answer 2

The decimal data type isn't supported in the default Jet 4.0 mdb file. You have to use the SQL Server compatibility syntax (ANSI 92) setting to use the decimal data type in the SQL Window.

Click on the menu, Tools > Options. Click on the Tables/Query tab. Mark the check box for "This database" in the SQL Server compatibility syntax (ANSI 92) section. This mode will affect the entire db, including queries with wildcards, so you may want to try this on a copy of your db.

Paste this into the SQL window:

ALTER TABLE MyTable
  Add COLUMN MyField DECIMAL (9,4) NULL;

If you don't want to alter the mode of your database, you must use vba code with the adodb library:

Dim conn As ADODB.Connection

Set conn = CurrentProject.Connection
conn.Execute "ALTER TABLE MyTable " _
    & "ADD COLUMN MyField DECIMAL (9,4) NULL;"
conn.Close

Answer 3

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