8

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.

Community
  • 1
  • 1
Steztric
  • 2,832
  • 2
  • 24
  • 43
  • 1
    The second one is standard SQL, but I don't believe that it's supported in Access. See [`ALTER TABLE`](http://msdn.microsoft.com/en-us/library/office/bb177883(v=office.12).aspx) – Damien_The_Unbeliever Dec 27 '12 at 15:46
  • http://stackoverflow.com/questions/10440990/default-clause-in-alter-table-statement-resulting-in-syntax-error. It can't be done using SQL. I have voted to close this question. – shahkalpesh Dec 27 '12 at 15:48
  • That previous question was about an Access 97 db. And since much of Access DDL capability (including `DEFAULT`) was not introduced until Jet 4 (Access 2000), the previous question applies here only if Steztric is also using Access 97 format as his db file. – HansUp Dec 27 '12 at 15:59
  • @Steztric What Access version is your db file? – HansUp Dec 27 '12 at 16:05
  • 2
    @shahkalpesh: Not to be closed, since this one has a better answerby Hansup, in my view. By the way, there's is no way voting *against* a close ?! – iDevlop Dec 27 '12 at 16:08
  • @HansUp Access 2003 I believe, although I am opening it in Access 2010 without upgrading (it may mess up our business logic, who knows?) – Steztric Dec 27 '12 at 16:25
  • @iDevlop: Right. There isn't a way to mark it for not closing it. am modifying the question add Access 2003 tag to it. – shahkalpesh Dec 27 '12 at 18:55
  • Please leave this tagged as ms-access because it applies across multiple Access versions. I included Access 2003 in the body of the question. – HansUp Dec 27 '12 at 19:30
  • @HansUp if your reopening was justified, please remove the link to the duplicate, otherwise please re-close the Question. – Cœur Jul 08 '18 at 16:20

2 Answers2

11

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thank you @HansUp, this is what I was looking for. In fact the reason I am executing SQL from within Access is as a sandpit area to ensure I was writing the correct ODB query to do this in code. – Steztric Dec 27 '12 at 16:15
  • You're welcome. I'm unsure whether that ALTER TABLE will work from an ODBC connection. If it does not, use an OleDb connection instead because it will work then. – HansUp Dec 27 '12 at 16:26
  • I already post this in another thread (http://stackoverflow.com/questions/13605663/how-to-add-a-boolean-column-to-ms-access-via-sql-in-vb-net/13606223#comment55764481_13606223). I just discovered that if you do an ALTER TABLE ADD for a BIT / YESNO column via a SQL window in Access (haven't tried through ADO but I suspect it will act the same way), for existing rows, it won't apply the DEFAULT supplied value if you set it to `TRUE`, but sets it to 0. New ones will, though. Thanks, MS! – Pere Dec 11 '15 at 14:16
0

It seems, there would be Constraint issue with your column. Although following DDL statement is the correct way.

ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES'

Reference

Ravi
  • 30,829
  • 42
  • 119
  • 173