Geek Answers Handbook

Error adding column using SQL command in MS Access 2003

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

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

Why doesn't it work? The following is the SQL I am using:

 ALTER TABLE EMP_2 ADD EMP_PCT NUMBER(4,2);
+4
source share
4 answers

Accessing access data types DDL can be difficult to sort. NUMBER actually creates the field as a float with double precision. But if you try to include the field size, precision or scale in parentheses after NUMBER , you 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 following statement adds a decimal data type column to MyTable with precision = 4 and scale = 2, which means that it will contain up to two 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 allows you to create a decimal field with ADO. The statement generates a syntax error if you try to execute it from the DAO, for example, using CurrentDb.Execute or from the Access query constructor. For more information, see Field Type Reference — Names and Values ​​for DDL, DAO, and ADOX .

As @ErikE explained, the decimal type of Access is problematic, especially with the Access 2003 that you use. Consider using a currency type field instead. It avoids the decimal creepy and provides better performance thanks to the db engine handling a fixed number of decimal places.

+2
source

Firstly, I recommend not using the Decimal data type in Access 2003, because there are errors with it when sorting (wrong order) and aggregation (amounts truncating the fractional part). In Access 2007, the general problem is solved, but not sorting (although you can probably fix this in Access 2007 by specifying the index in the column).

As for your script, there are two obvious problems:

  • You must use ADD COLUMN ColumnName not ADD ColumnName

  • The correct Decimal data type is because NUMBER instead creates a double precision float and does not allow parentheses after specifying any size. (Maybe NUMERIC will work as a synonym for 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 sent via DAO (as in CurrentDb.Execute ), but must be done via ADO ( CurrentProject.Connection.Execute ).

There seems to be a way to make this SQL work , but it requires changing the database settings:

The decimal data file type is not supported in the default MDd file of Jet 4.0. You must use SQL Server Compatibility Syntax (ANSI 92) to use the decimal data type in the SQL window.

Select the menu, Tools> Options. Click the Tables / Query tab. Check the box next to "This Database" in SQL Server Compatibility Syntax (ANSI 92). This mode will affect the entire db, including wildcard queries, so you can try this on a copy of your db.

If you still can't get it to work, you can 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:

+1
source

try it,

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

Or try using this url,

Adding fields

0
source

try it

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

More articles:


All Articles