Create Table on SQL Server

Does anyone know how to create a primary key field which automatically increments each time you add a new record?

This SQL works in an Access database:

CREATE TABLE News (NewsID AutoIncrement Constraint PrimaryKey PRIMARY KEY, OtherField TEXT (50))

But using SQL Server I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Column or parameter #1:
Cannot find data type AutoIncrement

Can you help?

I think you have to do it manually in the code. You'd have to get the ID number of the last record, up that by one, then place it on the next record.

I think you had better used Identity, check out your sql server help pages (it's really simple, go to the design of your table and check identity 1)


In your SQL you do the following (for example)

Insert into Clients (client_name) " &_
"Values ('" & strName & '") " &_
"Select @@identity as client_id"

hope I helped you out!

Thanks, I have got it to work now!!