How to specify default in SQL Create TABLE?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
Thanks Allen. That works and it does populate the Default field in Design View.
How do you set the Validation Rule?


Allen Browne said:
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.

AA2e72E said:
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?
Click to expand...
Click to expand...
 
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.

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


Allen Browne said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top