Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: Creating Access tables in SQL

    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.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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)
    )
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Posts
    8

    Talking

    Thanks for that works a treat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •