How to set the default value of an Access 2003 field using SQL?

Question

How can I set the default value for a field using SQL in MS Access?

I tried this but got a syntax error:

CREATE TABLE HELLO
( MUN INTEGER  NOT NULL,
ADD   CHAR(50) DEFAULT'16 asd ST.'
)

Answer 1

The word ADD is a keyword. Try this:

CREATE TABLE HELLO
( 
    MUN INTEGER  NOT NULL,
    [ADD] CHAR(50) DEFAULT '16 asd ST.'
)

Answer 2

The DEFAULT and CHAR keywords are only supported when in the ACE/Jet engine's ANSI-92 Query Mode (and then only in SQL DDL). As Jose Basilio points out, ADD is a reserved word and must be escaped using square brackets. Also, you need a space between the DEFAULT word and its clause (as Jose has shown).

If you are executing the SQL in a Query object in the MS Access interface you will need to change from the default (ANSI-89 Query Mode) to ANSI-92 Query Mode. See: About ANSI SQL query mode.

If you are creating the table programmatically e.g. you are using DAO then try using a CurrentProject.Connection.Execute "Sql goes here" where CurrentProject.Connection is an ADO classic or other OLE DB connection to your data source.

P.S. Surely you wanted you column to be HELLO.Mum :)