CREATE TABLE Query, DEFAULT value DateTime

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

Guest

I am putting together some scripts to run in VB to create an ACCESS database
and it's schema.

As a part of each table I want to have a timestamp field that on record
insertion defaults to the current datetime stamp.

What would the CREATE TABLE statement look like for this, assuming that it
can be done.

Thanks,

Gary Novosel
 
GaryN said:
I am putting together some scripts to run in VB to create an ACCESS
database and it's schema.

As a part of each table I want to have a timestamp field that on
record insertion defaults to the current datetime stamp.

What would the CREATE TABLE statement look like for this, assuming
that it can be done.

Thanks,

Gary Novosel
Click to expand...

You could try something like this

CREATE TABLE myTest
(id Counter PRIMARY KEY,
myDesc Varchar (25) NOT NULL,
myDate DATETIME DEFAULT Now())
 
For further clarification assume the following base CREATE query:

CREATE TABLE LogTable
(ID INTEGER PRIMARY KEY
,TimeStamp DATETIME DEFAULT ????????
)
 
That's it!

Thanks, Gary
--

Gary Novosel



RoyVidar said:
You could try something like this

CREATE TABLE myTest
(id Counter PRIMARY KEY,
myDesc Varchar (25) NOT NULL,
myDate DATETIME DEFAULT Now())
Click to expand...
 
GaryN said:
CREATE TABLE LogTable
(ID INTEGER PRIMARY KEY
,TimeStamp DATETIME DEFAULT ????????
)
Click to expand...

You should perhaps avoid the name 'timestamp'. The equivalent of Jet's
DATETIME data type in the SQL standards is TIMESTAMP; also, it is a
reserved word in Jet 4.0 and ODBC.

Jamie.

--
 
I'm using .ASP script to create a table in an MS Access database (ODBC).
I've tried:
MyTime DATETIME DEFAULT Now()
DEFAULT Now
DEFAULT (Now)
DEFAULT (Now())
And they all give me a syntax error. I've temporarily left out the DEFAULT
just so that I can develop the rest of the web project, but I really need to
have the current timestamp as the default value for MyTime column. Anybody
know what's wrong?
 
I'm using .ASP script to create a table in an MS Access database (ODBC).
I've tried:
MyTime DATETIME DEFAULT Now()
DEFAULT Now
DEFAULT (Now)
DEFAULT (Now())
And they all give me a syntax error. I've temporarily left out the DEFAULT
just so that I can develop the rest of the web project, but I really need to
have the current timestamp as the default value for MyTime column. Anybody
know what's wrong?
Click to expand...

I would guess the odbc driver doesn't support the DEFAULT keyword in
CREATE/ALTER TABLE SQL DDL (Jet 3.n engine certainly didn't) and
further guess the reason being the driver executes in ANSI-89 Query
Mode (see http://office.microsoft.com/en-gb/access/
HP030704831033.aspx).

FWIW the Jet 4.0/ACE ANSI-92 Query Mode syntax (e.g. using MS OLE DB
Providers) would be:

MyTime DATETIME DEFAULT NOW() -- not required
MyTime DATETIME DEFAULT NOW() NOT NULL -- required

Jamie.

--
 
Back
Top