SQL to add column with default value - Access 2003

Question

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!

Answer 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

Answer 2

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"

Answer 3

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 4

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.

Answer 5

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.