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

Question

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 1

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