SQL SET DEFAULT not working in MS Access

sql ms-access default alter-table alter-column
Steztric picture Steztric · Dec 27, 2012 · Viewed 13.8k times · Source

Possible Duplicate:
DEFAULT clause in ALTER TABLE statement resulting in syntax error

I am trying to execute the following statement using a SQL query within MS Access;

ALTER TABLE [table] ALTER COLUMN [column] SET DEFAULT 'default value'

However, I get a dialog displaying the error Syntax error in ALTER TABLE statement.

And when I click OK it highlights the word DEFAULT. I also tried the following statement;

ALTER TABLE [table]
ADD CONSTRAINT [Default] DEFAULT 'default value' FOR [column]

And I get another error Syntax error in CONSTRAINT clause.

What is the correct syntax for setting a default value in MS Access? The db file is Access 2003 format.

Answer

HansUp picture HansUp · Dec 27, 2012

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.

Related questions

How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More