Err Msg: Syntax Error in DROP TABLE Statement

ID: Q131629

The information in this article applies to:

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

SYMPTOMS

When you use version 2.0 of the Open Database Connectivity (ODBC) drivers to delete database tables, you may receive the following error message:

Syntax error in DROP TABLE statement

CAUSE

The ODBC drivers listed below will return the above error message 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.

RESOLUTION

This error is caused by the way the DROP TABLE statement is created in Microsoft Query. For example, if you are using the Microsoft dBASE ODBC version 2.0 driver to delete a table called "1TABLE.DBF" in the C:\TEST directory, the following SQL statement is created by Microsoft Query and used to drop the table:


   DROP TABLE c:\test\'1table.dbf'

NOTE: This select statement will cause the same error if you use it in the SQLExecQuery function in a Visual Basic for Applications macro.

This will cause the error stated above, unless you replace the backslash immediately before the table name with a period as shown below:


   DROP TABLE c:\test.'1table.dbf'

STATUS

Microsoft has confirmed this to be a problem in the Microsoft 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 5.00c

Keywords : xlquery
Version : 1.00
Platform : WINDOWS
Issue type :