Knowledgebase
Home » Item #993
Data tool error - 'Syntax error in string in query expression'
Question
I am using the data tool to import data from a spreadsheet. It keeps failing on a query that starts:
Error occurred!
Syntax error in string in query expression
INSERT INTO tblCactuShop1Products(...
Answer
The problem is the way the ODBC drivers for Excel handle the spreadsheet data.
The columns in Excel spreadsheets don't have set field sizes (unlike database fields), so the driver does a quick check of the first 8 rows and uses this to determine which field types and sizes to use.
If the first 8 records in the list contain a blank (or short) P_Desc field, this effectively sets the field size to the default (255). If you have any product descriptions further down the spreadsheet that are longer, these will then fail when the data tool tries to import them.
A workaround is to find the row that contains the longest P_Desc field and "cut and paste" it as the first row in the excel sheet. Alternatively you could add a row of data to the top and ensure that the description fields are long enough. This product can then be deleted once the import is complete.
Microsoft offers a fix by modifying the registry and setting the "Rows to Scan" key to a higher number.
(which is really not a proper fix but a hack!)
http://support.microsoft.com/kb/189897