Creating Access tables with SQL

I'm currently writing a web application in Coldfusion which uses a Access 2000 db. I can create tables in SQL ok but am having problems with the Autonumber type. Any ideas?uuuuuummmm...

maybe a little more details?

What error, what syntax...|||I think you are looking for the IDENTITY property. It's not a separate data type like in Access, but a property available for certain data types (principally INT) in SQL.

Do a search on Autonumber on this forum. I recently answered a similar question...

Regards,

hmscott|||Originally posted by Brett Kaiser
uuuuuummmm...

maybe a little more details?

What error, what syntax...

Good point, it was a bit vague.

Trying to create a table with field SID as an Autonumber and the primary key.

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="creattable_users" datasource="#attributes.dsn#">
Create Table #tbl.code#_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)
)
</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.|||Try SID INT IDENTITY (1,1)

Regards,

hmscott|||Originally posted by hmscott
Try SID INT IDENTITY (1,1)

Regards,

hmscott

Tried it but no joy.|||Did you try it this way?

Create Table #tbl.code#_Users
(
SID INT IDENTITY(1,1) NOT NULL,
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)
)

regards,

hmscott|||Did a copy and paste on your code, still throws up the same error.
Might have to convince the client to stop using Access.|||Managed to get the answer in another forum, in case anybody is interested the solution is:

SID COUNTER PRIMARY KEY

Thanks for all the help.