Microsoft KB Archive/131162

{|
 * width="100%"|

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

 * }

-

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.