Create Table & Provide Default Value

[zufie]

I want to use SQL code to Create a Table & Provide a Default Value for
Each Column.

Here is the code I am using:

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Mumbai',
Country char(25),
Birth_Date date)

What can I do to make this work with MS Access?

Thanks!,

zufie

[Lyle Fairfield]

On 23 Mar, 14:28, "zufie" <[e-mail]:

I want to use SQL code to Create a Table & Provide a Default Value for
Each Column.

Here is the code I am using:

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Mumbai',
Country char(25),
Birth_Date date)

What can I do to make this work with MS Access?

Thanks!,

zufie

TTBOMR one must use ALTER TABLE as in

"ALTER TABLE customer ALTER COLUMN FirstName SET DEFAULT 'Bubba'"

My own opinion is that when a default value is needed, it's best to
set this in the form or other user interface, or insert query.

[Mike Gramelspacher]

In article <1174674510.412904.62590
@y66g2000hsf.googlegroups.com>, jo***********@illinois.gov
says...

I want to use SQL code to Create a Table & Provide a Default Value for
Each Column.

Here is the code I am using:

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Mumbai',
Country char(25),
Birth_Date date)

What can I do to make this work with MS Access?

Thanks!,

zufie

This is by way of example. You might want to look at SQL
Programming Style by Joe Celko for guidance on column names and
data types.

Notice that I was able to insert a record with 3 NOT NULL
columns missing. This was thanks to the default values.

Sub CreateTables()

With CurrentProject.AccessConnection
.Execute _
"CREATE TABLE customer" & _
" (First_Name VARCHAR(50)," & _
" Last_Name VARCHAR(50)," & _
" Mail_Address VARCHAR(50) DEFAULT Unknown," & _
" City_Name VARCHAR(50) DEFAULT Mumbai," & _
" Country_Name VARCHAR(25) DEFAULT Unknown," & _
" Birth_Date DATE," & _
" UNIQUE (Last_Name, First_Name, Birth_Date))"

.Execute _
"INSERT INTO customer" & _
" ( First_Name, Last_Name, Birth_Date )" & _
" VALUES ('Larry', 'Smith', #11/29/1985#);"

End With

End Sub

[gramelsp]

On Mar 24, 6:37 am, Mike Gramelspacher <[e-mail]:

In article <1174674510.412904.62590
@y66g2000hsf.googlegroups.com>, [e-mail]
says...

I want to use SQL code to Create a Table & Provide a Default Value for
Each Column.

Here is the code I am using:

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Mumbai',
Country char(25),
Birth_Date date)

What can I do to make this work with MSAccess?

Thanks!,

zufie

This is by way of example. You might want to look at SQL
Programming Style by Joe Celko for guidance on column names and
data types.

Notice that I was able to insert a record with 3 NOT NULL
columns missing. This was thanks to the default values.

Sub CreateTables()

With CurrentProject.AccessConnection
.Execute _
"CREATE TABLE customer" & _
" (First_Name VARCHAR(50)," & _
" Last_Name VARCHAR(50)," & _
" Mail_Address VARCHAR(50) DEFAULT Unknown," & _
" City_Name VARCHAR(50) DEFAULT Mumbai," & _
" Country_Name VARCHAR(25) DEFAULT Unknown," & _
" Birth_Date DATE," & _
" UNIQUE (Last_Name, First_Name, Birth_Date))"

.Execute _
"INSERT INTO customer" & _
" ( First_Name, Last_Name, Birth_Date )" & _
" VALUES ('Larry', 'Smith', #11/29/1985#);"

End With

End Sub- Hide quoted text -

- Show quoted text -

I suppose my statement would make sense if I had used DDL like this:

..Execute _
"CREATE TABLE customer" & _
" (Customer_id IDENTITY (1,1) NOT NULL PRIMARY KEY, " & _
" First_Name VARCHAR(50) NOT NULL," & _
" Last_Name VARCHAR(50) NOT NULL," & _
" Mail_Address VARCHAR(50) DEFAULT Unknown NOT NULL," & _
" City_Name VARCHAR(50) DEFAULT Mumbai NOT NULL," & _
" Country_Name VARCHAR(25) DEFAULT France NOT NULL," & _
" Birth_Date DATE NOT NULL," & _
" UNIQUE (Last_Name, First_Name, Birth_Date))"

I guess you customers will always be persons and will never refuse to
disclose their birth date.