Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    20

    Unanswered: INSERT INTO w/ Autonumber field

    Im going crazy here and I hate access with a passion, but I do have a problem so here it is.

    I have an access DB called "Entrys" with 6 fields:

    ID (autonumber)
    First (rest are text)
    Last
    Company
    Code
    Phone
    Email


    The autonumber field is screwing me up somehow, here are the queries Ive tried:

    1 SQL:
    INSERT INTO Entrys VALUES ('First', 'Last', 'Company', '123', '555-555-5555', 'blah@killme.com')

    ERROR:
    "Number of query values and destination fields are not the same"


    2 SQL:
    INSERT INTO Entrys (first, last, company, code, phone, email) VALUES ('First', 'Last', 'Company', '123', '555-555-5555', 'blah@killme.com')

    ERROR:
    "Syntax error in INSERT INTO statement"


    3 SQL:
    INSERT INTO Entrys (ID, first, last, company, code, phone, email) VALUES ('123', 'First', 'Last', 'Company', '123', '555-555-5555', 'blah@killme.com')

    ERROR:
    This one actually works, I can assign the autonumber field a value, but thats not what I want. Why wont Access accept the second SQL query?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    something funky

    i just tested both your queries 2 and 3 in access 97 and they work fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    20
    Exactly! You would think Query 2 would work fine by leaving out the auotnumber field from the insert but I get an error everytime. Something is funky

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, when i tested it, i used text for all the columns except ID

    perhaps you could double-check your column definitions

    or maybe drop and recreate the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Posts
    20
    They are all text except for ID

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe drop and recreate the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2004
    Posts
    11
    It works for me as well. Just specify all of the columns except for the autonum PK field, and it will work for sure.

Posting Permissions

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