0

Can someone please have a look and let me know if you can see any problems with this INSERT statement? I am trying to insert the records using excel VBA to Access Database that has 124 columns and for some reason it is giving me an error “Syntax error in INSERT INTO statement”. Personally I do not see any issues with this statement however I am not sure if the insert statement can be that long for access database? I am out of ideas and I have been trying to figure this out for last 2 days...

Any help would be greatly appreciated! Many thanks

INSERT INTO DIBBS_Table (
    Email_ID
    ,Match_ID
    ,Cage
    ,Solicitation
    ,Solicitation_Type_Indicator
    ,Small_Business_Set_Aside_Indicator
    ,Additional_Clause_Indicator
    ,Return_By_Date
    ,Quoter_Cage_Code
    ,Quote_For_Cage_Code
    ,Reserved
    ,Reserved1
    ,Reserved2
    ,Reserved3
    ,Reserved4
    ,Small_Business_And_Other
    ,Small_Disadvantage
    ,Business_Ethinc
    ,Woman_Owned
    ,Veteran_Owned
    ,HUBZone
    ,HUBZone_Joint
    ,Participating_HUBZone
    ,Reserved5
    ,Compliance_Code
    ,Compliance_Reports_Code
    ,Alternate_Disputes_Resolution
    ,Bid_Type_Code
    ,Payment_Discount_Terms_Code
    ,Vendor_Quote_Number
    ,Days_Quote_Valid
    ,Packaging_Requirement
    ,Ordering_Agreement
    ,BOA_FSS_BPA _Contract_Number
    ,BOA_FSS_BPA_Contract_Expiration_Date
    ,FOB_Point
    ,FOB _City
    ,FOB_State
    ,FOB_Country
    ,Inspection_Point_Code
    ,Place_of_Government_Inspection_Packaging_CAGE_code
    ,Supplies_CAGE_code
    ,SPI_Process_Proposed
    ,SPI_Process
    ,SPI_Facility
    ,SPI_Military_Federal
    ,SPI_Affected_Contract_Line
    ,Solicitation_Line_Number
    ,Reserved6
    ,Purchase_Request_Number
    ,NSN
    ,Unit_of_Issue
    ,Quantity
    ,Unit_Price
    ,Delivery_Days
    ,Guaranteed_Minimum
    ,O_Min
    ,Contract_Max
    ,AFB
    ,O_DO_Min_Quantity
    ,Reserved7
    ,Reserved8
    ,Immediate_Shipment_Price
    ,Immediate_Shipment_Deliver_Days
    ,Reserved9
    ,Trade_Agreements_Indicator
    ,Source_Of_Supply_Cage_Code
    ,First_Article_Waiver_Code
    ,Hazardous_Material
    ,Hazardous_Warning_labels
    ,Material_Requirements
    ,Buy_American_Indicator
    ,Free_Trade_Agreements_Indicator
    ,BA_FT_TA_End_Product
    ,BA_FT_T_Country_Of_Origin
    ,BA_FT_T_Country_Code
    ,Duty_Free_Entry_Requested
    ,Duty_Paid_Code
    ,Duty_Paid_Amount
    ,Price_Breaks_Solicited_Indicator
    ,Range_Lower
    ,Range_Upper
    ,Range_Unit_Price
    ,Range_2_Lower
    ,Range_2_Upper
    ,Range_2_Unit_Price
    ,Range_3_Lower
    ,Range_3_Upper
    ,Range_3_Unit_Price
    ,Range_4_Lower
    ,Range_4_Upper
    ,Range_4_Unit_Price
    ,Range_5_Lower
    ,Range_5_Upper
    ,Range_5_Unit_Price
    ,Rage_6_Lower
    ,Range_6_Upper
    ,Range_6_Unit_Price
    ,Quantity_Variance_Plus
    ,Quantity_Variance_Minus
    ,Minimum_Order_Quantity_Code
    ,Max_Quantity
    ,Immediate_Shipment_Available
    ,Immediate_Shipment_Quantity
    ,Manufacturer_Dealer
    ,Production_Source_CAGE_code
    ,Production_Source_Name_and_Address
    ,Item_Description_Indicator
    ,Part_Number_Offered_Code
    ,Part_Number_Offered_CAGE_code
    ,Part_Number
    ,Offered_Remarks
    ,Supplies_Offered
    ,Supplies_Offered_Remarks
    ,MFG_Cage
    ,Requirements_Source_CAGE
    ,Requirements_Item_Name
    ,Requirements_Service_Identification
    ,Requirements_Test_Number
    ,Higher_Level_Quality_Indicator
    ,Higher_Level_Quality_Code
    ,Higher_Level_Quality_Remarks
    ,Child_Labor_Certification_Code
    ,Quote_Remarks
    )
VALUES (
    '2530002703878SPE7M320U1910100010273306721'
    ,'2530002703878SPE7M320U19101000102733'
    ,'6721'
    ,'SPE7M320U1910'
    ,''
    ,'R'
    ,'N'
    ,'27/08/2020'
    ,'7QEZ4'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'R'
    ,'N'
    ,''
    ,''
    ,''
    ,'NA'
    ,'NA'
    ,'A'
    ,'BI'
    ,'1'
    ,''
    ,'90'
    ,'Y'
    ,'NAP'
    ,''
    ,''
    ,'O'
    ,'KANSASCITY'
    ,'MO'
    ,'US'
    ,'D'
    ,'6721'
    ,'6721'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'1'
    ,''
    ,'1000102733'
    ,'2530002703878'
    ,'EA'
    ,'3736'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'N'
    ,'6721'
    ,''
    ,''
    ,''
    ,'0'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'N'
    ,''
    ,''
    ,''
    ,'N'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'0'
    ,'0'
    ,'N'
    ,''
    ,''
    ,''
    ,'DD'
    ,'6721'
    ,''
    ,'D'
    ,'1'
    ,'6721'
    ,'01358600A0'
    ,''
    ,'1'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'7'
    ,'7'
    ,''
    ,'U'
    ,''
    )
Improve this question
4
  • Can Access do stored procedures? Commented Aug 15, 2020 at 20:49
  • 1
    poorsql.com to format sql (now you can count selected lines easy on an editor like Notepad++). Quite sure not all fields contain text. E.g Email_ID is a number and needs to be inserted without quotes enclosed. How is statement executed? Commented Aug 15, 2020 at 20:59
  • 2
    Open MS Access, create a query, switch into SQL mode, paste your query and execute. With a bit of luck if it has an error it will position itself on the bad line. @MathieuGuindon No acces doesnt have SP. But you can execute SP of other RDBMS with VBA. You can also write VBA functions that would mimic a SP and call it from your SQL, depends on the stuff to do. Commented Aug 15, 2020 at 21:01
  • THank you for the suggestions guys. Much appreciated! Commented Aug 16, 2020 at 0:58

2 Answers 2

Reset to default
3

You are inserting everything as text which is very unlikely how it should be.

Double-check the data type of every field and adjust, for example for a numeric field, a Null value, and a date:

,6721
,'SPE7M320U1910'
,Null
,'R'
,'N'
,#2020/08/27#
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I add everything as a text intentionally, I know it is not a good practice but in this instance is does not matter. Thank you for the suggestion anyway.
Pure luck, and it may only seem to work. Put that practice away better today than tomorrow before it bites you.
3

I think you need to remove the space in the following field names

BOA_FSS_BPA _Contract_Number
FOB _City
Improve this answer

2 Comments

Good catch - very likely this is it. Note, you should be able to quote the identifiers with square brackets too: BOA_FSS_BPA _Contract_Number, assuming that's the actual field name.
Yes, that was it! Very good catch!! However, for some reason the column name was not valid and I had to change the name of few columns to get it to work and it is finally working! I was looking at this for few days and I could not find, lol, this was something that was very obvious! THANK YOU VERY MUCH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.