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

-Syntax error in FROM clause

-Syntax error in INSERT statement.

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

Method 3: Add brackets ([) around the words "Database" or "Table." For example, to select all records from a table called Database, follow these steps:

1. In Microsoft Query, click the SQL button on the toolbar.
   
   This displays the SQL dialog box.



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

   
                   SELECT * FROM [Database] 




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



4. Choose Yes to continue.
   
   This displays only the Data pane in the query window.

Additional query words: 2.00 MSQuery

Keywords : xlquery
Version : WINDOWS:1.0,2.0
Platform : WINDOWS
Issue type :