I'm using Coldfusion and trying to create a Access table with a autonumber field (SID).

I'm using the following query but to be honest I haven't got a clue how to set the first field (SID) to Autonumber

<cfquery name="createtable_users" datasource="#attributes.dsn#">
Create Table #tbl.code#_Users
(
SID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)
</cfquery>



The code in my SQL book is designed purely for SQL DBs and is not having any of it.

The error is -

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.


SQL = "Create Table test1_Users ( SID INT NOT NULL PRIMARY KEY DEFAULT 1, Firstname VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, Address VARCHAR(150) NOT NULL, Town VARCHAR(50) NOT NULL, County VARCHAR(50) NOT NULL, Postcode VARCHAR(50) NOT NULL, email VARCHAR(50), phone VARCHAR(50) NOT NULL, mobile VARCHAR(50) )"



Which is really helpful as you can see.

Thanks in advance for any help.

Create Table #tbl.code#_Users
(
SID COUNTER PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)

Thanks for that works a treat.