Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Lightbulb Unanswered: INSERT INTO syntax error

    Hi,

    I'm using Access 2010 and I'm getting this error with the INSERT INTO statement. This is what I have.
    -----------------------------------------
    INSERT INTO LensData (CompanyID,LensDesign,LensNotes,LensTypeID,LensCat egoryID,LensMaterialID,FinishTypeID,MatIndexID,Mat AbbeID,MatDensityID,GeometryTypeID,Photochromic,Ph otoBrand,Polarized,PolarBrand,ARCoat,ARBrand,Scrat chCoat,ScratchBrand,UVProtect,Features,FilterColor s,FitHeightIDMin)

    SELECT [Display Name],[Lens Design],[Lens Notes],[Lens Type],[Lens Category],[Lens Material],[Finish Type],MatIndex,MatAbbe,MatDensity,GeometryType,Photochr omic,PhotoBrand,Polarized,PolarBrand,ARCoat,ARBran d,ScratchCoat,ScratchBrand,UVProtect,Features,Filt erColors,Fit

    FROM NewsStuff;
    -----------------------------------
    Now it all looks fine to me but I'm hearing things about certain properties that columns may have that would cause this to be an error, I don't know, but is this syntax correct?

    And what should I be aware of when it comes to INSERT INTO statements?

    Thanks.

  2. #2
    Join Date
    Jul 2009
    Posts
    39
    For some reason some of the rows in that query are showing spaces, I don't have them as spaces so you can ignore that part.

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You're not attempting to INSERT Values into a Field in LensData that is defined as an AutoNumber Datatype, are you? That always gets the Access Gnomes in an uproar!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Jul 2009
    Posts
    39
    No I'm not. The only AutoNumber DataType is the primary key, "ID" which isn't being inserted anywhere.

  5. #5
    Join Date
    Jul 2009
    Posts
    39
    Or is your question, am I trying to insert data into LensData that is defined as AutoNumber in LensData?

  6. #6
    Join Date
    Jul 2009
    Posts
    39
    After double checking, that isn't the case. So no I'm inserting or attempting to insert into an AutoNumber field.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Where exactly and how are you trying to execute this?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Jul 2009
    Posts
    39
    I figured it out...it turns out that Access isn't smart enough to generate primary key ID values.

    So that has to be included in the insert into statement, of course you need to go to the receiving table, find out the last primary key ID, and use that value, incremented by 1, and so on and so on forth for the new values that are going to be inserted.

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Access is smart enough to generate primary key ID values, if the table in question has an AutoNumber field that is nominated as the primary key. We have a set of databases that have worked this way for over a decade.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Jul 2009
    Posts
    39
    The primary key ID value in the destination table is an autonumber, but it still needs to be included in the insert statement because it won't generate it off of the insert without it included.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by queenzNYdude View Post
    The primary key ID value in the destination table is an autonumber, but it still needs to be included in the insert statement because it won't generate it off of the insert without it included.
    That would be the first time in almost 20 years that I would see such a case. Not very likely!

    Please post the definition (columns names and data types + indexes) of the table LensData.
    Have a nice day!

Posting Permissions

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