SQL to Add Column with Default Value - Access 2003

SQL to add column with default value - Access 2003

Tools -> Options -> Tables/Queries -> (At the bottom right:) Sql Server Compatible Syntax - turn option on for this database.

then you can execute your query:

ALTER TABLE documents ADD COLUMN membersOnly NUMBER DEFAULT 0

How to add NUMBER column with default value?

Default only works when it's used in DDL executed from an ADO connection. The syntax for this, which I have tested would be this:

CurrentProject.Connection.Execute "ALTER TABLE testTable ADD COLUMN testColumn NUMBER"
CurrentProject.Connection.Execute "ALTER TABLE testTable ALTER COLUMN testColumn SET DEFAULT 0"

Also, this only sets the default for the table for all new records, and does not modify existing records to 0. If that is also your intention, simply run an update query, like this one.

strSQL = "UPDATE testTable SET testTable.testColumn = 0;"
CurrentDb.Execute strSQL

Database Access Mdb how to set a default value to existing column via SQL

I solved using AxBase from SourceForge.net.

I could properly open the *.mdb database and run the above SQL commands and they worked perfectly:

 ALTER TABLE Members ALTER COLUMN Privacy SET DEFAULT 1

SQL SET DEFAULT not working in MS Access

Support for DEFAULT was included in Access DDL with Jet 4 (Access 2000). However it can only be used in DDL executed from an ADO connection.

This worked with Access 2007.

CurrentProject.Connection.Execute "ALTER TABLE MyTable " & _
    "ALTER COLUMN field2 SET DEFAULT ""foo"";"

Note if your db file is Access 97 or earlier, you won't be able to set a field DEFAULT value from DDL.

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.

SQL Access 2003 create column if exisit value

use the iif condition that will replace null with a value, and use left outer join since normal join is filtering those rows without a match. A left join will keep them with the values NULL

IIF(ISNULL(DOWN.DOWN_SUM),0,DOWN.DOWN_SUM)

 SELECT 
    Product.ID_Product, 
    IIF(ISNULL(UP.UP_SUM),0,UP.UP_SUM), 
    IIF(ISNULL(DOWN.DOWN_SUM),0,DOWN.DOWN_SUM), 
    IIF(ISNULL(UP.UP_SUM),0,UP.UP_SUM)-IIF(ISNULL(DOWN.DOWN_SUM),0,DOWN.DOWN_SUM) AS TOTAL_SUM,
    FROM (PRODUCT LEFT OUTER JOIN UP ON Product.ID_Product = UP.ID_Product)
    LEFT OUTER JOIN DOWN ON Product.ID_Product = DOWN.ID_Product;

---

Related Topics