6

Updating an old ASP/Access site for a client - I need SQL to add a column to an existing table and set a default value. Doesn't work - any ideas?

This works fine

ALTER TABLE documents ADD COLUMN membersOnly NUMBER

I want this to work:

ALTER TABLE documents ADD COLUMN membersOnly NUMBER DEFAULT 0

Have googled and seen instructions for default values work for other field types but I want to add number. Thanks!

Zam
  • 2,880
  • 1
  • 18
  • 33
Polsonby
  • 22,825
  • 19
  • 59
  • 74
  • Thanks, [that worked perfectly](http://stackoverflow.com/questions/47535/sql-to-add-column-with-default-value-access-2003#47552). But it means I have to download the MDB to do it and I wanted the SQL so that I didn't have to take it offline! I worked out that YESNO has an intrinsic default value so I can get away with that instead on this occasion. Great to know about the ANSI compatible mode though - many thanks indeed. – Polsonby Sep 06 '08 at 15:35

5 Answers5

8

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
zappan
  • 3,668
  • 4
  • 29
  • 24
  • 1
    This doesn't work for me. It creates column but doesn't set default. – JohnMerlino Feb 01 '11 at 01:10
  • +1 Thanks, didn't know about this option. It's not necessary to simply add a DEFAULT clause, and it doesn't let you add a default CONSTRAINT using the standard syntax, but I'm sure it will be useful for something. – harpo Feb 05 '11 at 17:17
  • 5
    The setting in Access options is only relevant to operations on the database that are done through the Access user interface (and within Access it has a number of unfortunate side effects that can break a working Access application). If you want SQL 92 syntax from outside Access, just use ADO to execute the DDL statement. – David-W-Fenton May 22 '11 at 00:08
7

With ADO, you can execute a DDL statement to create a field and set its default value.

CurrentProject.Connection.Execute _
   "ALTER TABLE discardme ADD COLUMN membersOnly SHORT DEFAULT 0"
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • 1
    And since it's via ADO, it will use SQL 92 mode. Much of the other information in the answers in this question is sheer rubbish. – David-W-Fenton May 22 '11 at 00:09
1

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

is not found on MS Access 2010

user3921919
  • 21
  • 1
0

You may find Sql Server Compatible Syntax is already turned on, so definately worth just trying to run the sql statement mentioned above (via an ADO connection from ASP) before resorting to taking the db offline. Thanks, this helped me out.

mike nelson
  • 21,218
  • 14
  • 66
  • 75
0

How are you connecting to the database to run the update SQL? You can use the ODBC compatible mode through ADO. Without opening the database in Access.