MS Access Create a table with auto-increments and default date

Question

I am trying to create an MS access table with auto-increment identifier and the "Default date" field, but the following query always says "Syntax error in CREATE TABLE." Statement:

CREATE TABLE Table1 ( [ID] AUTOINCREMENT, [Email] TEXT(255), [ProductID] NUMBER, [DateCreate] DATETIME, [DateSend] DATETIME ); ALTER TABLE Table1 ALTER [DateSend] DATETIME DEFAULT NOW() NOT NULL; 

Who can help me fix this request. Thanks!

Answer 1

There are many NUMBER types in Ms-Access, so you have to be specific. I think you want Integer .

 CREATE TABLE Table1 ( [ID] AUTOINCREMENT, [Email] TEXT(255), [ProductID] INTEGER, [DateCreate] DATETIME, [DateSend] DATETIME ); 

ALTER TABLE syntax requires ALTER COLUMN :

 ALTER TABLE Table1 ALTER COLUMN [DateSend] DATETIME DEFAULT NOW() NOT NULL; 

---

You can also have these two in one statement:

 CREATE TABLE Table1 ( [ID] AUTOINCREMENT, [Email] TEXT(255), [ProductID] INTEGER, [DateCreate] DATETIME, [DateSend] DATETIME DEFAULT NOW() NOT NULL ); 

---

It is best to have a PRIMARY KEY for each table, and you probably planned this for an ID :

  [ID] AUTOINCREMENT PRIMARY KEY, 

---

Page with lots of useful information on how to handle Access with SQL:

Intermediate Microsoft Jet SQL for Access 2000

Answer 2

 CREATE TABLE Tblcontact ( contactid AUTOINCREMENT PRIMARY KEY , firstname CHAR (60), lastname CHAR (60), email VARCHAR (75) );