How to specify default in SQL Create TABLE?

[AA2e72E]

Is there any way to specify field defaults in SQL Create table? E.G. with
CREATE TABLE [MYTABLE] (MYFIELD DECIMAL(20,2))
how do I specify the field MYFIELD to have a default value of 100.75?

 

[Allen Browne]

In JET 4 (Access 2000 and later), you can use the DEFAULT keyword to specify
a literal value (but not a function AFAIK).

You may find that this does not work in the query window, and you have to
execute it under ADO. Example:
strSql = "CREATE TABLE [MYTABLE] (MYFIELD DECIMAL(20,2) DEFAULT
100.75);"
CurrentProject.Connection.Execute strSql

You probably need to know that Access is incapable of handling fields of
type Decimal correctly. Details in:
Incorrect Sorting (Decimal fields)
at:
http://members.iinet.net.au/~allenbrowne/bug-08.html

 

[AA2e72E]

Thanks Allen. That works and it does populate the Default field in Design View.
How do you set the Validation Rule?

In JET 4 (Access 2000 and later), you can use the DEFAULT keyword to specify
a literal value (but not a function AFAIK).

You may find that this does not work in the query window, and you have to
execute it under ADO. Example:
strSql = "CREATE TABLE [MYTABLE] (MYFIELD DECIMAL(20,2) DEFAULT
100.75);"
CurrentProject.Connection.Execute strSql

You probably need to know that Access is incapable of handling fields of
type Decimal correctly. Details in:
Incorrect Sorting (Decimal fields)
at:
http://members.iinet.net.au/~allenbrowne/bug-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is there any way to specify field defaults in SQL Create table? E.G. with
CREATE TABLE [MYTABLE] (MYFIELD DECIMAL(20,2))
how do I specify the field MYFIELD to have a default value of 100.75?

 

[Allen Browne]

AFAIK, you cannot set a Validation Rule using a DDL query statement.

Use DAO, like this:
CurrentDb().TableDefs("MyTable").Fields("MyField").ValidationRule =
"Between 1 and 100"

For this particular property, you could also use ADOX to set the "Jet
OLEDB:Column Validation Rule" Property of the Column in the Table in the
Catalog. However, ADOX is incomplete, inconsistent between versions, buggy,
and subject to reference problems so we use it only where we are absolutely
forced to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. That works and it does populate the Default field in Design
View.
How do you set the Validation Rule?

In JET 4 (Access 2000 and later), you can use the DEFAULT keyword to
specify
a literal value (but not a function AFAIK).

You may find that this does not work in the query window, and you have to
execute it under ADO. Example:
strSql = "CREATE TABLE [MYTABLE] (MYFIELD DECIMAL(20,2) DEFAULT
100.75);"
CurrentProject.Connection.Execute strSql

You probably need to know that Access is incapable of handling fields of
type Decimal correctly. Details in:
Incorrect Sorting (Decimal fields)
at:
http://members.iinet.net.au/~allenbrowne/bug-08.html

Is there any way to specify field defaults in SQL Create table? E.G.
with
CREATE TABLE [MYTABLE] (MYFIELD DECIMAL(20,2))
how do I specify the field MYFIELD to have a default value of 100.75?