Problem accessing xls spreadsheet from ODBC database adapter when Table name contains a space

The following steps may be used to recreate the problem.

- Set up a system DSN referencing an Excel .xls file with a table name that contains a space.
- Configure the MDQ file using the above system DSN.
- Create a simple SELECT query like: SELECT * FROM 'TABLE 1$'
- Generate a type tree for the query, which will cause an error to occur.
- The ODBC database adapter trace will show the following error:

Message: [Microsoft][ODBC Excel Driver] Syntax error in query. Incomplete query clause.
SQL State: S1000

The problem occurs when a query is used to reference an .xls table name which contains a space and the standard single quote syntax is not supported by the ODBC driver.

If you run a database query from Database Interface Designer or run a DBQUERY / DBLOOKUP function from a map rule and the above error occurs, then review the query to determine whether the table name contains a space.

The problem may be resolved by surrounding the table name in the query with a set of double quotes in addition to the existing single quotes.

Database Interface Designer Query example:

SELECT * FROM "'Sheet 1$'"

In a map rule, the double quote characters may need to be represented as the Hex 22 value in order to distinguish them from the existing double quotes that surround the entire SQL query.

Map rule (using Hex 22 value for the double quotes) example:

=DBLOOKUP("select * from <<22>>'Sheet 1$'<<22>>","-DBTYPE odbc -SOURCE LocalExcel -T")