Ms Access Create Table with Autoincrement and Default Date

MS Access Create Table with Autoincrement and default date

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

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

The ALTER TABLE syntax requires ALTER COLUMN :

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

---

You could also have those two in one statement:

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

---

It's best practise to have a PRIMARY KEY on every table, and you probably intended that for the ID:

    [ID] AUTOINCREMENT PRIMARY KEY,

---

A page with a lot of useful information about how to handle Access with SQL:

Intermediate Microsoft Jet SQL for Access 2000

Access sql for create table with autonumber

AUTOINCREMENT and integer are two different datatypes as far as Access DDL is concerned. Use only AUTOINCREMENT. And to make it function correctly as an autonumber, include the PRIMARY KEY constraint.

This one works without error when tested with ADO/OleDb in Access 2010:

CREATE TABLE testallcols(SOCycle Text(3), AutoKey AUTOINCREMENT PRIMARY KEY, SOData LongBinary NOT NULL)

How to Auto Increment a Date Field in an Access Table

If this is a one-time thing then it's probably easiest just to take Gustav's suggestion and create a VBA routine that loops through a DAO.Recordset to update the dates. However, since you're using Access_2013 I thought I'd mention another approach: an event driven Data Macro.

If you define the following Before Change data macro on the table

then any time you add a new row for an existing [CampaignID] with a NULL [WeekEnded] column the data macro will populate [WeekEnded] with the value for the following week.

If you create the macro on an existing table and want to update the existing rows then you could simply run the UPDATE query

UPDATE TheTable SET CampaignID = CampaignID WHERE WeekEnded IS NULL;

Syntax for AUTOINCREMENT column for MS Access and ODBC

AUTOINCREMENT is a field type based on Long Integer, so INTEGER AUTOINCREMENT amounts to asking for 2 field types. However, Access only lets you designate 1 type per field.

Include PRIMARY KEY so the field will operate as a proper Access autonumber. (Without PRIMARY KEY, the table would accept duplicate FunkyCol values.)

CREATE TABLE [FunkyTable] (FunkyCol AUTOINCREMENT PRIMARY KEY);

Using a MS Access query to auto increment my primary key

I worked it out myself. If anybody has a similar problem, it was the order of the words that was the problem.

ALTER TABLE Unified_Backlog_Shipping
ADD P_Key AUTOINCREMENT PRIMARY KEY;

How to create an AutoNumber field value in Access?

According to SQL Auto Increment a Field:

CREATE TABLE Persons
(
P_Id PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The MS Access uses the AUTOINCREMENT
keyword to perform an auto-increment
feature.

By default, the starting value for
AUTOINCREMENT is 1, and it will
increment by 1 for each new record.

To specify that the "P_Id" column
should start at value 10 and increment
by 5, change the autoincrement to
AUTOINCREMENT(10,5).

Synonyms for AUTOINCREMENT include COUNTER and IDENTITY. Using IDENTITY the makes a lot of sense because it matched the @IDENTITY variable which returns the last used autonumber value.

SQL create table and set auto increment value without Alter table

Yes you can do it while creating the table as

CREATE TABLE Persons (
     ID int NOT NULL AUTO_INCREMENT,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255),
     Address varchar(255),
     PRIMARY KEY (ID)
)AUTO_INCREMENT=100;

Here is a test case

mysql> CREATE TABLE Persons (
    ->      ID int NOT NULL AUTO_INCREMENT,
    ->      LastName varchar(255) NOT NULL,
    ->      FirstName varchar(255),
    ->      Address varchar(255),
    ->      PRIMARY KEY (ID)
    -> )AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into Persons (LastName) values ('CCC');
Query OK, 1 row affected (0.03 sec)

mysql> select * from Persons ;
+-----+----------+-----------+---------+
| ID  | LastName | FirstName | Address |
+-----+----------+-----------+---------+
| 100 | CCC      | NULL      | NULL    |
+-----+----------+-----------+---------+
1 row in set (0.00 sec)

---

Related Topics