autoincrement in access sql is not working

Question

How can I create a table with autoincrement in access. Here is what I have been doing but not working.

CREATE TABLE People_User_Master(  
    Id INTEGER primary key AUTOINCREMENT,
    Name varchar(50),
    LastName varchar(50), 
    Userid varchar(50) unique,
    Email varchar(50),
    Phone varchar(50),
    Pw varchar(50),
    fk_Group int,
    Address varchar(150)  
)

Solution 1

Try adding the constraint at the end

CREATE TABLE People_User_Master(   
Id AUTOINCREMENT 
  , Name varchar(50)   
, LastName varchar(50)   
, Userid varchar(50) unique   
, Email varchar(50)   
, Phone varchar(50)   
, Pw varchar(50)   
, fk_Group int   
, Address varchar(150)
, CONSTRAINT id_pk PRIMARY KEY(Id)

)

Updated to fit the actual answer (the definition of INTEGER on the AUTOINCREMENT column was not allowed). Leaving PRIMARY KEY at the same line as Id AUTOINCREMENT does work.

Solution 2

Remove INTEGER (it conflicts with AUTOINCREMENT)

CREATE TABLE People_User_Master(  
Id AUTOINCREMENT primary key ,
Name varchar(50),
LastName varchar(50), 
Userid varchar(50) unique,
Email varchar(50),
Phone varchar(50),
Pw varchar(50),
fk_Group int,
Address varchar(150)  

)

Solution 3

You can do it using IDENTITY (supported by Jet4+)

CREATE TABLE People_User_Master
(
ID IDENTITY (1, 1), 
Name ..

Failing that;

ID AUTOINCREMENT, 

Should work (note you don't specify a type)

Solution 4

It may be working, but appears to fail if attempting an INSERT INTO with ID in the Column clause. This seems to override MS Access AUTOINCREMENT.

This attempts to insert a record with ID=1 bypassing AUTOINCREMENT

INSERT INTO People_User_Master 
       (Id, Name, LastName, Userid, Email, Phone, Pw, fk_Group, Address)
VALUES (1, "John", "Smith", "JS100", "[email protected]", 12345678, "****","","")

Omitting ID lets AUTOINCREMENT function properly.

INSERT INTO People_User_Master 
       (Name, LastName, Userid, Email, Phone, Pw, fk_Group, Address)
VALUES ("John", "Smith", "JS100", "[email protected]", 12345678, "****","","")

View more solutions

Comments

[user254875486]

Ok, try removing the INTEGER after Id. (so the second line becomes Id AUTOINCREMENT

[Thunder]

bingo ! it works ... please edit your answer so that I can accept it. Id AUTOINCREMENT PRIMARY KEY also works Thanks

[phillipsK]

How do I INSERT INTO while omitting values and having MS Access SQL select an autoincrement value or a 'Next Value For' SQL Server like syntax for the ID sequence value for a destination MS Access Table?