"Syntax error in CREATE TABLE statement" with ODBC 2.0 Drivers

 Last reviewed: September 12, 1996
Article ID: Q131162
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 create database tables, you may receive the following error message: 

   Syntax error in CREATE TABLE statement
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 dBASE Driver
   Microsoft FoxPro Driver
   Microsoft Paradox Driver
These drivers are shipped with the Microsoft ODBC Desktop Database Drivers Kit.

CAUSE

This error is caused by the way the CREATE TABLE statement is created in Microsoft Query.

RESOLUTION

If you are using the dBASE ODBC driver to create a table called 1TABLE with a field called FIELD1 in the C:\TEST directory, the following SQL statement is created by Microsoft Query and is used to make the table: 

   CREATE TABLE c:\test\'1TABLE' (FIELD1 Char(5))

   NOTE: This select statement will cause the same error if you use it in
   the SQLExecQuery function in a Visual Basic for Applications macro.
To work around this problem, replace the backslash immediately before the table name with a period as shown below: 

   CREATE TABLE c:\test.'1TABLE' (FIELD1 Char(5))

STATUS

Microsoft has confirmed this to be a problem in the ODBC Desktop Database Drivers Kit version 2.0. This problem was fixed in the ODBC Desktop Database Drivers Kit version 3.0.

REFERENCES

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

ARTICLE-ID: Q124319 

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

KBCategory: kb3rdparty kbinterop
KBSubcategory: xlquery

Additional reference words: 1.00 5.00 5.00c 2.00 Structured Query Language
VBA xlquery



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.