Error adding column using SQL command in MS Access 2003

Question

I want to add a column to my EMP_2 table. The new EMP_PCT column will store a percentage as a number, for example 20% as 20.0.

I tried to input this method. But it gave a syntax error in the ALTER TABLE statement.

Why is it not working? Below is the SQL I am using:

ALTER TABLE EMP_2
ADD EMP_PCT NUMBER(4,2);

Are you trying to **add** a new column or **alter** an existing column? — ErikE, Dec 28 '12 at 07:29
i am going to alter table EMP_2 to include a new column named EMP_PCT with data type Number. THis EMP_PCT will store percentage number. for example : 20.00 — user1933884, Dec 28 '12 at 07:40
If it is percentage, wouldn't `(5,2)` be more appropriate to allow up to 100.00? — ErikE, Dec 28 '12 at 07:41
you are right erike, but the problem is the method NUMBER(4,2) does not work — user1933884, Dec 28 '12 at 07:50

HansUp · Accepted Answer · 2012-12-28T09:00:39.030

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.

That was a copy and paste from the command which worked on my Access 2003 system. I changed only the table name and the data type. I have no idea it fails for you. — HansUp, Dec 28 '12 at 07:56

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

First, I recommend that you not use the Decimal data type in Access 2003 because there are bugs with it around sorting (wrong order) and aggregating (sums truncating the fractional portion). In Access 2007 the aggregate problem is solved but not the sorting (though you can supposedly fix this in Access 2007 by putting an index on the column).

As for your script, there are two obvious problems:

You must use ADD COLUMN ColumnName not ADD ColumnName
The correct data type is DECIMAL, because NUMBER creates a double-precision float instead, and doesn't allow parentheses after it specifying any kind of size. (Maybe NUMERIC would work as a synonym of DECIMAL but I don't know.)

So this should work for you:

ALTER TABLE EMP_2 ADD COLUMN EMP_PCT DECIMAL(4,2);

According to HansUp and other sources, this cannot be submitted through DAO (as in CurrentDb.Execute) but must be done via ADO (CurrentProject.Connection.Execute).

Apparently, there is a way to get this SQL working but it requires a database settings change:

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.

If you still cannot get things working, you might consider this method (thanks to Philippe Grondier):

Dim TD As DAO.TableDef
Dim F As DAO.Field

Set TD = CurrentDb.TableDefs("TableName")
Set F = TD.CreateField("FieldName", dbDecimal, 4)
F.DecimalPlaces = 2
F.DefaultValue = 0

TD.Fields.Append F

For reference, here are some related Microsoft.com help pages:

i just tried that method. It appears syntax error in field definition — user1933884, Dec 28 '12 at 07:44
@user1933884 Please see my update. Can you use normal VB code or must it be a SQL Statement? — ErikE, Dec 28 '12 at 07:59

Mariappan Subramanian · Answer 3 · 2012-12-28T07:39:56.540

0

try this,

ALTER TABLE EMP_2 
ADD COLUMN EMP_PCT NUMBER(4,2);

Or else try using this URL,

AddingFields

edited Dec 28 '12 at 07:39

answered Dec 28 '12 at 07:25

Mariappan Subramanian

9,527
8
32
33

You are adding a column or altering a existing column? – Mariappan Subramanian Dec 28 '12 at 07:35
i want to make a new column that have not existed yet – user1933884 Dec 28 '12 at 07:36
U are using ms access 2003 right? I have added a url which will give you the UI level description to add column – Mariappan Subramanian Dec 28 '12 at 07:39

score 0 · Answer 4 · answered Dec 28 '12 at 07:27

0

try this

   ALTER TABLE EMP_2 ADD (EMP_PCT NUMBER (4,2));

answered Dec 28 '12 at 07:27

Nidhish Krishnan

20,593
6
63
76

Error adding column using SQL command in MS Access 2003

4 Answers4