Microsoft Access DateTime Default Now via SQL

Question

I am writing a number of scripts to update numerous Access tables. I would like to add a column to each that has a field called "date_created" that is a timestamp of when the record is created. Doing this through the table view is simple, simply set the DefaultValue = now(). However, how would I accomplish this in sql?

This is my current attempt for tables that already have the column. This example uses "tblLogs".

ALTER TABLE tblLogs ALTER COLUMN date_created DEFAULT now()

Thanks for your help!

Update - Would there be a way to do this in VBA?

Update 2 - Tested all of the answers and the following by onedaywhen is the shortest and most accurate

CurrentProject.Connection.Execute _
    "ALTER TABLE tblLogs ALTER date_created DATETIME DEFAULT NOW() NOT NULL;"

Answer 1

Instead of now() you can use getdate(). Can't check other part of syntax (no sql here and I rarely change tables :)), but should be about same.

edit: Seems that SQL doesn't allow change defaults so easily. Look at this page, where practical example is provided - you need to change constraints, because MSSQL treats defaults as constraints. Sorry for misinformation.

Answer 2

In SQL Server it would be:

ALTER TABLE tblLogs ALTER COLUMN date_created DEFAULT getDate()

Answer 3

I suggest that to create or restructure Jet tables, you use DAO instead of DDL. It offers the ability to control properties that are lacking in Jet's implementation of DDL. The Access help should provide you what you need on this.

Answer 4

I'm assuming:

• your target database is the ACE or Jet engine, rather than SQL Server;
• you want the date and time i.e. what is known as CURRENT_TIMESTAMP in Standard SQL (not directly supported by ACE/Jet, though) and not to be confused with SQL Server's TIMESTAMP data type;
• you want an answer using SQL DDL.

If you created your table using this SQL DDL:

CREATE TABLE tblLogs 
(
   date_created DATETIME NOT NULL
);

then the following SQL DDL would add the DEFAULT your require:

ALTER TABLE tblLogs ALTER 
   date_created DATETIME DEFAULT NOW() NOT NULL;

The above ACE/Jet SQL syntax is ANSI-92 Query Mode flavour. To use this via the MS Access interface (e.g. a Query object's SQL view) see Microsoft Office Access: About ANSI SQL query mode (MDB). To do this programmatically using SQL DDL requires (AFAIK) the use of OLE DB, which in VBA requires (AFAIK) the use of ADO. DAO always uses ANSI-89 Query Mode syntax, whose SQL DDL syntax lacks support for DEFAULT.

If you are using Access (rather than ACE/Jet standalone), you can use a single line of VBA (ADO) code:

CurrentProject.Connection.Execute "ALTER TABLE tblLogs ALTER date_created DATETIME DEFAULT NOW() NOT NULL;"