change the LONG after [ID] in the CREATE TABLE statement to INT IDENTITY.
INT is the SQL Server name for a Long data type in Access, and IDENTITY is
the keyword in SQL Server for denoting an AutoNumber field.

My book, SQL Server Development with Access 2000, includes a table
summarizing comparable data type names from Access and SQL Server.  It also
includes how to use the IDENTITY property in different context, including
how to turn it off so you can sometimes write a value into a column with the
IDENTITY  property.  I mention this latter capability of the IDENTITY
property to point out that it is not exactly the same as an AutoNumber
field.

Since you said you were using Access 2000, I want to remind you that you can
create tables graphically, using either the visual Table Designer or the a
Database Diagram.  Any SQL Server database can have multiple database
diagrams.  They are kind of like the Relationship window in Access on
steroids.  My book contains a whole chapter on how to use database diagrams
from the Database window in an Access project.

I hope this reply helps.

Rick Dobson
www.programmingmsaccess.com
www.cabinc.net
rickd@c... <mailto:rickd@c...>



-----Original Message-----
Subject: how to create a table with autonumber and primary key?
From: "yuenkit" <janet_smith2000@y...>
Date: Tue, 20 Mar 2001 16:29:39
X-Message-Number: 3

i use Access2000
my sql statement is like this
strSQL = "CREATE TABLE Janet ([ID] LONG PRIMARY KEY, [NAME]
VARCHAR(50));"
objRS.Open strSQL, objConn

after executed,
i want to insert data:
strSQL = "INSERT INTO Janet([NAME]) VALUES('Smith');"
objRS.Open strSQL, objConn

then a message was appeared:
primary key [ID] cann't contain null value.

i thought primary key field is autonumbered automatically each time i
insert new record.

my question is:
how do we create a autonumber field with primary key?
thanks in advance