Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL to add column with default value - Access 2003

Tags:

sql

ms-access

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!

like image 726
Polsonby Avatar asked Sep 06 '08 15:09

Polsonby


2 Answers

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
like image 109
zappan Avatar answered Sep 28 '22 02:09

zappan


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"
like image 29
HansUp Avatar answered Sep 28 '22 02:09

HansUp