Microsoft KB Archive/131920

{|
 * width="100%"|

"Syntax error in FROM clause" Using ODBC 2.0 Drivers

 * }

5.00 5.00c WINDOWS kbtool xlquery

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows NT, version 5.0
 * Microsoft Query for Windows, version 1.0

-

SYMPTOMS
When you use version 2.0 of the Open Database Connectivity (ODBC) drivers with the products listed above, you may receive the following error message:

Syntax error in FROM clause

This problem will occur with the drivers listed below if the first character of a table's name is a number (for example, "1TABLE"):

Microsoft Btrieve Driver

Microsoft dBASE Driver

Microsoft FoxPro Driver

Microsoft Paradox Driver

Microsoft Text Driver These drivers are shipped with the Microsoft ODBC Desktop Database Drivers Kit.

CAUSE
The error is caused by the way the SQL SELECT statement is created in Microsoft Query. To illustrate the problem, assume you are using the dBASE ODBC driver to select data from a file called 1TABLE.DBF in the C:\TEST directory. The following SELECT statement is created by Microsoft Query to retrieve the data when you add a field name to the data pane:

  SELECT '1test'. FIELD1 FROM c:\test\'1test.dbf' '1test' NOTE: This select statement will cause the same error if you use it in the SQLExecQuery macro function in a Visual Basic for Applications macro.

WORKAROUND
To work around this problem, do the following:

 Add any tables you need to the table pane. From the Records menu, choose the Automatic Query option.

This turns off Microsoft Query's ability to automatically query the table each time you add a field or criteria to the data or criteria panes. Add any fields you need to the data pane. Also, add any fields you need to the criteria pane.  From the View menu, choose the SQL command. In the SQL dialog box, change the SQL statement's FROM clause by replacing the backslash immediately before the table name with a period as shown below:      SELECT '1test'.FIELD1 FROM c:\test.'1test.dbf' '1test'   Choose OK. When you receive the following error message, choose OK:

SQL Query can't be represented graphically. Continue anyway?



The data will be displayed, but the table and criteria panes will not be displayed.

STATUS
Microsoft has confirmed this to be a problem in the ODBC Desktop Database Drivers Kit, version 2.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.