Q131920 - "Syntax error in FROM clause" Using ODBC 2.0 Drivers

"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.

REFERENCES

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

Q124319 BUG: Syntax Error When Using Qualified Table Name with Quotes

Additional query words: 2.00 Structured Query Language

Keywords : xlquery
Version : WINDOWS:1.0,5.0,5.0c; winnt:5.0
Platform : WINDOWS winnt
Issue type :
Technology :