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

    Question Unanswered: 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?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    uuuuuummmm....

    maybe a little more details?

    What error, what syntax....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    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

  4. #4
    Join Date
    Feb 2004
    Posts
    8
    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.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Try SID INT IDENTITY (1,1)

    Regards,

    hmscott

  6. #6
    Join Date
    Feb 2004
    Posts
    8
    Originally posted by hmscott
    Try SID INT IDENTITY (1,1)

    Regards,

    hmscott
    Tried it but no joy.

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    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

  8. #8
    Join Date
    Feb 2004
    Posts
    8
    Did a copy and paste on your code, still throws up the same error.
    Might have to convince the client to stop using Access.

  9. #9
    Join Date
    Feb 2004
    Posts
    8
    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.

Posting Permissions

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