Good afternoon, I am attempting to import a excel datasheet into excel. The 3075-Syntex error (missing operator) in query expression. The error is showing that it is in the first box of the first row when converting to Access.
Parameters:
tblImportProduct has only one field of DocumentID.
I have attmepted to change the field source data type short text, long text and number
The field column in excel example is: 1625700_10726_20210114182539052
Dim strColumnAcleaned As String
Dim strSqlDml As String
Dim intLine As Long
The code is long, but begins with allowing the end user to chose the spreadsheet, goes directly to the workbook and begins to import: That all works! its when it begins to convert that I get the error code. Eventually I want to add 10 additional columns and was having problems with the date doing the same thing. I eliminated all the columns and am just trying to start by getting this first column to work.
This is where I think the error is located:
Do
strColumnAcleaned = Replace(xlWs.Cells(intLine, 1).Value2, "'", """") 'replace single quotes in column A value if additional rows then can replace commas by dots as decimal separator in specified columns
strSqlDml = "INSERT INTO tblImportProduct(DocumentID) VALUES('" & strColumnAcleaned & ")" 'creates a SQL insert statement using the previous obtained cleaned variables and the value for column A. The insert statement must have the sequence present in the destination table and is obtained by concatenating values per each line presented in the Excel file while iterating
Debug.Print intLine & " - " & strSqlDml
CurrentDb.Execute strSqlDml, dbFailOnError 'executes the insert statement against the database, the dbFailOnError is an optional value that will make the Execute process return an error if the SQL was not properly executed
intLine = intLine + 1
Loop Until IsEmpty(xlWs.Cells(intLine, 1)) 'stopping criteria, when values in column A stop the loop will stop,
'please note in cells collection the first index is the row and the second one the column so we are making row changing.
'Once the loop stops the steps after close the open workbook, quit excel and clean the memory references to the created objects
Any help would be greatly appreciated