MSQuery Err: "Syntax Error . . . " Using ODBC Driver

• Microsoft Query for Windows, versions 1.0, 2.0

SYMPTOMS

In Microsoft Query, if you have a table with the name "Database" or "Table," and you try to add a field from the table to the Data pane, you may receive either of the following error messages:

-Syntax error

   -or-

You may also receive one of the following error messages when inserting, updating or deleting records in a database table:

-Syntax error in INSERT statement.

-or-

-Syntax error in UPDATE statement.

CAUSE

Microsoft Query creates a Structured Query Language (SQL) statement as you add, delete, or modify any criteria or fields in a query. "The syntax rules for SQL prohibit the use of reserved SQL words, such as "database" or "table", as table names. Also, reserved SQL words, such as "date" or "integer", cannot be used as field names."

This problem will occur under the following circumstances:

• You are using an ODBC 3.0 driver, and the table that you are working with is named either "Database" or "Table."

• You are using a Microsoft Excel data source that accesses an MS Excel 3.0 or 4.0 worksheet file containing the range names "Database" or "Table," you will receive the error shown above.
• You are using an MS Excel 5.0 workbook that contains a global range name called "Database" or "Table."

WORKAROUND

To keep from receiving this error message, use either of the following methods.

Method 1: Name the range something other than "Database" or "Table."

Method 2: If you are accessing data from a Microsoft Excel 5.0 workbook

          that contains a global range named "Database" or "Table," define
          it as a local range name, such as "Sheet1!Database." Note that
          making the name a local one will cause the table name to be
          displayed with the sheet name and then the range name:
          Sheet1!Database.

          example, to select all records from a table called Database,
          follow these steps:

          a. In Microsoft Query, click the SQL button on the toolbar.

             This displays the SQL dialog box.

          b. In the dialog box, type the following SQL statement:

                SELECT * FROM [Database]

          c. Choose OK.

          A message will appear stating that the query cannot be
          represented graphically.

          d. Choose Yes to continue.

          This displays only the Data pane in the query window.