CODEWITHSUNDEEP
sqldatabasems-access
Thunder
Thunder

Reputation: 10996

autoincrement in access sql is not working

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)  
)

Upvotes: 2

Views: 16186

Answers (4)

Craig Hatmaker
Craig Hatmaker

Reputation: 81

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, "****","","")

Upvotes: 1

user3887340
user3887340

Reputation: 21

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)

)

Upvotes: 2

user254875486
user254875486

Reputation: 11240

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.

Upvotes: 1

Alex K.
Alex K.

Reputation: 175776

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)

Upvotes: 1

Related Questions