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]

Re: Create Table & Provide Default Value

On 23 Mar, 14:28, "zufie" <john.marru...@ illinois.govwro te:

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]

Re: Create Table &amp; Provide Default Value

In article <1174674510.412 904.62590
@y66g2000hsf.go oglegroups.com> , john.marruffo@i llinois.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. AccessConnectio n
.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@psci.net]

Re: Create Table &amp; Provide Default Value

On Mar 24, 6:37 am, Mike Gramelspacher <grame...@psci. netwrote:

In article <1174674510.412 904.62590
@y66g2000hsf.go oglegroups.com> , john.marru...@i llinois.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 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. AccessConnectio n
.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.