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.'
)
views:
371answers:
2
+3
A:
The word ADD is a keyword. Try this:
CREATE TABLE HELLO
(
MUN INTEGER NOT NULL,
[ADD] CHAR(50) DEFAULT '16 asd ST.'
)
Jose Basilio
2009-04-27 02:52:39
Your SQL is correct but must be executed in ANSI-92 Query Mode. The default/traditional ANSI-89 Query Mode's SQL DDL syntax does not support the DEFAULT keyword (nor CHAR, come to that).
onedaywhen
2009-04-27 12:55:34
And the reason it doesn't is because the much richer DAO interface layer was intended by MS for this kind of thing. Jet's DDL support has always been something of a poor relation to the higher-level interface.
David-W-Fenton
2009-04-27 19:59:32
For the creation/alteration of ACE/Jet tables, DDL is richer than DAO. Take the above example: the CHAR(50) column is a fixed width data type. The DAO interface layer was not enhanced for this data type, whereas SQL DDL was. DAO can't even create a CHAR(50) column via SQL DDL! There are many other Jet 4.0 features that are missing from DAO: WITH COMPRESSION, CHECK constraints, fast foreign keys, etc. I think we both know that in the Jet 4.0 era it was rather ADO and SQL DLL that was "intended by MS for this kind of thing". Why else would they fail to enhance DAO accordingly? Forgetfullness?!
onedaywhen
2009-04-28 07:42:40
+2
A:
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 :)
onedaywhen
2009-04-27 10:54:06
If you're using DAO, then you can just use DAO to alter your table, instead of mucking around with changing to ADO just so you can run DDL.
David-W-Fenton
2009-04-27 20:01:03
Question was "using SQL..." (using DAO is not using SQL, IMO) "...in MS Access" which I took to mean "using the MS Access user interface" e.g. a Query object's SQL view. I agree there's no need to switch technology/methodology to create a DEFAULT, however one cannot create a CHAR(50) column at all using DAO.
onedaywhen
2009-04-28 07:32:01