syntax error in INSERT INTO statement

[Shaves]

OK.......I'm offiicially stuck at this point. I have this sql statement to insert a new record with 67 fields. Following is the sql code:

vsql = "INSERT into [GpDataDownloadFile] ([FTPFileDate], [GroupA], [Region], [UNIT], [TAXABLE], [BTName], [BTAddress], [BTCityStateZip], [ServiceLoc], [AcctNo]," & _
"[InvoiceNo], [InvWorkDate], [STName], [STAddress], [STCityStateZip], [StoreNo], [StoreType], [StoreCity], [StoreState], [StoreInvWorkDate]," & _
"[InvoiceBilledDate], [INVOICECOMMENTS], [PURCHASEORDER], [VEHICLEREGIONNUMBER], [VEHCILEGROUPNUMBER], [SSUED12DIGITBILLING#], [FSID], [STORENAME], [STOREADDRESS1], [STOREADDRESS2]," & _
"[STOREPHONENUMBER], [DRIVER], [VEHICLEMILEAGE], [VEHCILEIDENTIFICATIONNUMBER], [VEHICLEMAKE], [VEHICLEMODEL], [VEHCILEYEAR], [TRANSACTIONINITIATION], [VEHICLELICENSE], [CARRYOUTSALE]," & _
"[TIRETREADDEPTHS], [InvSlsTaxAmt], [InvExciseTaxAmt], [InvAmountDue], [InvRebateDiscAmt], [InvTruckTireIncentiveAmt], [InvDueDate], [InvCashDiscountAmt], [InvVolumeBonusAmt], [InvoiceLineNo]," & _
"[Units], [ArticleNo], [ArticleDescr], [UnitPrice], [ExciseTaxPerUnit], [BillingClass], [SalesTaxCode], [ExtendedAmount], [RebateUnits], [RebateDiscountFactor]," & _
"[RebateExtUnitPrice], [DiscountExtendedAmount], [TaxDescr], [TaxAmount], [TaxExtendedAmount], [NetDue], [ItemID]" & _
"Values(""" & UpL(x, 1) & """, """ & UpL(x, 2) & """, """ & UpL(x, 3) & """, """ & UpL(x, 4) & """, """ & UpL(x, 5) & """, """ & UpL(x, 6) & """, """ & UpL(x, 7) & """, """ & UpL(x, 8) & """, """ & UpL(x, 9) & """, """ & UpL(x, 10) & """, """ _
& UpL(x, 11) & """, """ & UpL(x, 12) & """, """ & UpL(x, 13) & """, """ & UpL(x, 14) & """, """ & UpL(x, 15) & """, """ & UpL(x, 16) & """, """ & UpL(x, 17) & """, """ & UpL(x, 18) & """, """ & UpL(x, 19) & """, """ & UpL(x, 20) & """, """ _
& UpL(x, 21) & """, """ & UpL(x, 22) & """, """ & UpL(x, 23) & """, """ & UpL(x, 24) & """, """ & UpL(x, 25) & """, """ & UpL(x, 26) & """, """ & UpL(x, 27) & """, """ & UpL(x, 28) & """, """ & UpL(x, 29) & """, """ & UpL(x, 30) & """, """ _
& UpL(x, 31) & """, """ & UpL(x, 32) & """, """ & UpL(x, 33) & """, """ & UpL(x, 34) & """, """ & UpL(x, 35) & """, """ & UpL(x, 36) & """, """ & UpL(x, 37) & """, """ & UpL(x, 38) & """, """ & UpL(x, 39) & """, """ & UpL(x, 40) & """, """ _
& UpL(x, 41) & """, """ & UpL(x, 42) & """, """ & UpL(x, 43) & """, """ & UpL(x, 44) & """, """ & UpL(x, 45) & """, """ & UpL(x, 46) & """, """ & UpL(x, 47) & """, """ & UpL(x, 48) & """, """ & UpL(x, 49) & """, """ & UpL(x, 50) & """, """ _
& UpL(x, 51) & """, """ & UpL(x, 52) & """, """ & UpL(x, 53) & """, """ & UpL(x, 54) & """, """ & UpL(x, 55) & """, """ & UpL(x, 56) & """, """ & UpL(x, 57) & """, """ & UpL(x, 58) & """, """ & UpL(x, 59) & """, """ & UpL(x, 60) & """, """ _
& UpL(x, 61) & """, """ & UpL(x, 62) & """, """ & UpL(x, 63) & """, """ & UpL(x, 64) & """, """ & UpL(x, 65) & """, """ & UpL(x, 66) & """, """ & UpL(x, 67) & """)"

When the code hits the following line, I get the "syntax error in INSERT INTO statement" message.

Set RecSet = Connection.Execute(vsql, dbrows, adCmdText Or adExecuteNoRecords)

Can anyone see a problem with the sql statement. If there is a better way to send the data to Access, I would appreciate seeing those suggestions as well. I appreciate the help.......

 

[CaptainD]

Not that I studied the whole SQL statement but you are missing a ")" after the fields.

vsql = "INSERT into [GpDataDownloadFile] ([FTPFileDate], [GroupA], [Region], [UNIT], [TAXABLE], [BTName], [BTAddress], [BTCityStateZip], [ServiceLoc], [AcctNo]," & _
"[InvoiceNo], [InvWorkDate], [STName], [STAddress], [STCityStateZip], [StoreNo], [StoreType], [StoreCity], [StoreState], [StoreInvWorkDate]," & _
"[InvoiceBilledDate], [INVOICECOMMENTS], [PURCHASEORDER], [VEHICLEREGIONNUMBER], [VEHCILEGROUPNUMBER], [SSUED12DIGITBILLING#], [FSID], [STORENAME], [STOREADDRESS1], [STOREADDRESS2]," & _
"[STOREPHONENUMBER], [DRIVER], [VEHICLEMILEAGE], [VEHCILEIDENTIFICATIONNUMBER], [VEHICLEMAKE], [VEHICLEMODEL], [VEHCILEYEAR], [TRANSACTIONINITIATION], [VEHICLELICENSE], [CARRYOUTSALE]," & _
"[TIRETREADDEPTHS], [InvSlsTaxAmt], [InvExciseTaxAmt], [InvAmountDue], [InvRebateDiscAmt], [InvTruckTireIncentiveAmt], [InvDueDate], [InvCashDiscountAmt], [InvVolumeBonusAmt], [InvoiceLineNo]," & _
"[Units], [ArticleNo], [ArticleDescr], [UnitPrice], [ExciseTaxPerUnit], [BillingClass], [SalesTaxCode], [ExtendedAmount], [RebateUnits], [RebateDiscountFactor]," & _
"[RebateExtUnitPrice], [DiscountExtendedAmount], [TaxDescr], [TaxAmount], [TaxExtendedAmount], [NetDue], [ItemID])" & _
"Values(""" & UpL(x, 1) & """, """ & UpL(x, 2) & """, """ & UpL(x, 3) & """, """ & UpL(x, 4) & """, """ & UpL(x, 5) & """, """ & UpL(x, 6) & """, """ & UpL(x, 7) & """, """ & UpL(x, 8) & """, """ & UpL(x, 9) & """, """ & UpL(x, 10) & """, """ _
& UpL(x, 11) & """, """ & UpL(x, 12) & """, """ & UpL(x, 13) & """, """ & UpL(x, 14) & """, """ & UpL(x, 15) & """, """ & UpL(x, 16) & """, """ & UpL(x, 17) & """, """ & UpL(x, 18) & """, """ & UpL(x, 19) & """, """ & UpL(x, 20) & """, """ _
& UpL(x, 21) & """, """ & UpL(x, 22) & """, """ & UpL(x, 23) & """, """ & UpL(x, 24) & """, """ & UpL(x, 25) & """, """ & UpL(x, 26) & """, """ & UpL(x, 27) & """, """ & UpL(x, 28) & """, """ & UpL(x, 29) & """, """ & UpL(x, 30) & """, """ _
& UpL(x, 31) & """, """ & UpL(x, 32) & """, """ & UpL(x, 33) & """, """ & UpL(x, 34) & """, """ & UpL(x, 35) & """, """ & UpL(x, 36) & """, """ & UpL(x, 37) & """, """ & UpL(x, 38) & """, """ & UpL(x, 39) & """, """ & UpL(x, 40) & """, """ _
& UpL(x, 41) & """, """ & UpL(x, 42) & """, """ & UpL(x, 43) & """, """ & UpL(x, 44) & """, """ & UpL(x, 45) & """, """ & UpL(x, 46) & """, """ & UpL(x, 47) & """, """ & UpL(x, 48) & """, """ & UpL(x, 49) & """, """ & UpL(x, 50) & """, """ _
& UpL(x, 51) & """, """ & UpL(x, 52) & """, """ & UpL(x, 53) & """, """ & UpL(x, 54) & """, """ & UpL(x, 55) & """, """ & UpL(x, 56) & """, """ & UpL(x, 57) & """, """ & UpL(x, 58) & """, """ & UpL(x, 59) & """, """ & UpL(x, 60) & """, """ _
& UpL(x, 61) & """, """ & UpL(x, 62) & """, """ & UpL(x, 63) & """, """ & UpL(x, 64) & """, """ & UpL(x, 65) & """, """ & UpL(x, 66) & """, """ & UpL(x, 67) & """)"

 

[JerryKlmns]

Many fields there...

I would open a forward-only, optimistic-locking, server-side, empty recordset. Then use the .AddNew method and assign the values to the fields one by one inside a With ... End With block for the recordset object. Of course you also need to use the Update method at the end.

Any way, this

Set RecSet = Connection.Execute(vsql, dbrows, adCmdText Or adExecuteNoRecords)

is not efficient because the SQL statement is an Insert which doesnt return any records thus the resulting recordset is empty. Instead I would use this

Cnn.Execute vsql,, 129 'adCmdText + adExecuteNoRecords

And I would avoid using reserved words like Connection to point to my connection object but rather Cnn

 

[Andrzejek]

1. After building your INSERT SQL, I would do:

Debug.Print vsql

This way it is A LOT EASIER to see what you're doing wrong.

2. Also, if you INSERT all fields into a table, you may just say:

INSERT INTO TableName VALUES(1, 2, 'abc')

As long as you have ALL fields, in right order and right type. That will make your insert sql a lot shorter.


Have fun.

---- Andy