SQL to add column with default value - Access 2003

Question

Updating an old ASP/Access site for a client (and hating it) - 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!

Answer 1

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

Answer 2

You can create a field with DDL, but you will need DAO to set the default value:

 ALTER TABLE documents ADD COLUMN membersOnly INT

Or DOUBLE, if you wish.

Then:

  Set db = CurrentDB
 db.TableDefs("documents").Fields("membersOnly").Properties("DefaultValue") = "0"

Answer 3

Thanks, that worked perfectly. 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.

Answer 4

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.

Answer 5

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.