CODEWITHSUNDEEP
sqldatabasems-access-2010
user1068295
user1068295

Reputation: 11

auto increment in ms access by sql query

I've tried this query in MS Access SQL query section

create table demo(
  deomid long not null identity(1,1),
  name varchar(200),

  primary key(userid)
)

I also have used identity , autonumber , autoincrement , autoincrement(1,1) , auto_increment instead of identity(1,1) in the above query.

Every statement produces error saying that syntax error in create table statement... Please tell what is proper way to auto increment a long field.

Upvotes: 0

Views: 7615

Answers (5)

Ives Leonidas
Ives Leonidas

Reputation: 1

CREATE TABLE Demo
( 
    [USER_ID] counter PRIMARY KEY, 
    [NAME] varchar(200) null,  
    [NAME_2] varchar(150) not null,  
    [NUMBER] double null,  
    [DATE] datetime not null
)

Upvotes: 0

Deep
Deep

Reputation: 31

You can try this 

CREATE TABLE EMPLOYEE 
{
   Emp_ID int NOT NULL IDENTITY(1,1)
}

Upvotes: 0

faheem khan
faheem khan

Reputation: 481

Try this out.

CREATE TABLE [demo] ([deomid] AUTOINCREMENT )

Upvotes: 3

onedaywhen
onedaywhen

Reputation: 57023

The IDENTITY keyword and any of its synonyms that involve its seed and increment values require ANSI-92 Query Mode.

Also, you can't create a PRIMARY KEY on userid because it isn't a valid column name. Perhaps you meant to use deomid? Try this:

CurrentProject.Connection.Execute _
    "create table demo( " & _
    "deomid long not null identity(1,1), " & _
    "name varchar(200), " & _
    "primary key(deomid) " & _
    ")"

Note your table still lacks a business key i.e. to be able to identify users in the real world. I would guess there should be a UNIQUE constraint on name (which should probably not be nullable!)

Upvotes: 0

f0rza
f0rza

Reputation: 480

from here http://www.datanamic.com/support/msaccess-autoinc.html

Use the COUNTER data type when you want to define an auto increment/auto number/identity column in MS Access. The COUNTER data type provides a monotonically increasing sequence of long integers for a column in a MS Access database.

Upvotes: 0

Related Questions