How to Create a Decimal Field in Access with Alter Table

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

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

Error adding column using SQL command in MS Access 2003

Access DDL data type names can be challenging to sort out. NUMBER actually creates a field as double precision float. But if you try to include field size, precision, or scale in parentheses after NUMBER, you will get a syntax error. The following statement creates a double field whether you execute it from ADO or DAO:

ALTER TABLE MyTable ADD COLUMN EMP_PCT NUMBER;

The next statement adds a decimal data type column to MyTable with precision = 4 and scale = 2, which means it will hold up to 2 digits to the left of the decimal point and 2 to the right.

CurrentProject.Connection.Execute "ALTER TABLE MyTable ADD COLUMN EMP_PCT DECIMAL (4,2);"

I used CurrentProject.Connection to execute the DDL statement because it is an ADO object, and Access SQL only allows you to create a decimal field with ADO. The statement will trigger a syntax error if you attempt to execute it from DAO, like with CurrentDb.Execute or from the Access query designer. See Field type reference - names and values for DDL, DAO, and ADOX for more information.

As @ErikE explained, Access' decimal type is problematic, especially with Access 2003 which you're using. Consider whether you might be able to use a currency type field instead. It avoids decimal buginess and offers faster performance due to the way the db engine handles the fixed number of decimal places.

CREATE TABLE with DECIMAL column using Access ODBC

There are a number of deficiencies with DDL handling in the Access ODBC driver and unfortunately you have encountered one of them. If you want to create a table with a DECIMAL column you will need to use adodbapi from pywin32 (release 211 or later) and the Access OLEDB provider to execute the CREATE TABLE statement:

# -*- coding: utf-8 -*-
import adodbapi  # (from pywin32)
conn_str = (
    r"Provider=Microsoft.Jet.OLEDB.4.0;"
    r"Data Source=C:\Users\Public\2003test.mdb;"
    )
conn = adodbapi.connect(conn_str)
crsr = conn.cursor()
sql = """\
CREATE TABLE [test] (
    [FID] INT,
    [AREA] FLOAT,
    [CODE] VARCHAR(10),
    [X_CO] DECIMAL(10,3))
"""
crsr.execute(sql)
conn.commit()
crsr.close()
conn.close()

Is it possible to alter column to decimal in MS Access 2007+?

Its not possible in query of ms access but in OLEDB its possible.

Creating a Table but getting Syntax error in field definition

You can use Currency to get you over this detail.

Create Table Order_Line
(
Order_Num Char(5),
Item_Num Char(4),
Num_Ordered Currency,
Quoted_Price Currency,
Primary Key (Order_Num, Item_Num)
);

Currency is like Decimal (15,4)

I think your original sql statement would have worked in previous versions of Access.
The Manual Query Designer will still allow you to change the decimal places per field for Number and Large Number.

Alter Access DB Precision property

Decimal fields aren't well supported with JET and DAO. Try connecting either via an ADO/ADODB connection:

Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection
cn.Execute "ALTER TABLE RESP ALTER COLUMN darbskaits DECIMAL(10,3);"
cn.Close

---

Related Topics