Last post Jun 10, 2012 07:56 AM by shahnet
Apr 21, 2012 12:15 PM|shahnet|LINK
I have a SQL error in the select statement when I try importing data from a a CSV (delimited with comma) to MDB.
Code under buttons:
'This one works
strSQL = "INSERT INTO [target] (targetname, targetvalue) SELECT targetname, targetvalue FROM[Text;HDR=YES;DATABASE=C:\accessdb;].[test.csv]"
'This one doesn't work
strSQL = "INSERT INTO [Sheet1] (Campaign,Date,SPS,Account,PubCost,Clicks,Impr,QA,ACV,LoginConfirmationConv,Status,AvgPos)
SELECT Campaign,Date,SPS,Account,PubCost,Clicks,Impr,QA,ACV,LoginConfirmationConv,Status,AvgPos FROM[Text;HDR=YES;DATABASE=C:\accessdb;].[test2.csv]"
The headers match. The database location is fine. HDR is on. The 2nd one gives me an error. I don't know if it's the data itself or the format of the database.
The error is:
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: Syntax error in
INSERT INTO statement.
Line 49: Dim objCommand As New
Line 50: objCommand.CommandText = strSQL
Line 51: objCommand.ExecuteNonQuery()
Line 52: cnn.Close()
Line 53: End Sub
Yes, it's 1.1. I cannot change version.
Apr 21, 2012 05:21 PM|Mikesdotnetting|LINK
Syntax error in INSERT INTO statement.
See here: http://www.mikesdotnetting.com/Article/76/80040E14-MS-Access-Syntax-Error-messages and then check the word "Date" in the list of reserved words for
Apr 22, 2012 10:17 PM|Decker Dong - MSFT|LINK
strSQL = "INSERT INTO [Sheet1] (Campaign,Date,SPS,A……
Suspecting it that you've used a key word——Date！
So in order to avoid this problem：
1）Please set around your field names with a pair of ""。
2）Do please to put the Access query in the form of SQL into Access and run it first and then paste it into your codes。
Jun 10, 2012 07:56 AM|shahnet|LINK
Got this one sorted out - it was the date. Thank you.