Arvi Laanemets wrote:
IMHO you can use only non-dynamic named ranges, defined as direct
cell
references, as source tables for ODBC query.

Either a TABLE or a SYSTEM_TABLE may be used in a query using the ODBC
driver. The same applies to the OLE DB providers, accept they cannot
differentiate between TABLE or SYSTEM_TABLE.

There is only one definition for TABLE, being a workbook-level defined
Name ('named range'), defined using a simple formula to return the
range e.g. =Sheet1!A1:B4. I assume the driver/provider reads the
address used in the formula (but at a low level). Cells are not
calculated when data is accessed using Jet e.g. you can change a
precedent but the formula cell's value will not reflect the change
until it is opened and calculated in the Excel UI (also, a formula cell
cannot be changed using Jet). Therefore, a dynamic range which relies
on the result of a formula will not be seen as a TABLE.

There are three definitions for SYSTEM TABLE

1) a worksheet-level defined Name, again defined using a simple formula
to return the range e.g.

SELECT * FROM [MySheet$MyDefinedName];

2) a worksheet e.g.

SELECT * FROM [MySheet$];

The UsedRange (at a lower level than VBA) determines the table bounds.

3) an absolute range address, either with an explicit sheet name e.g.

SELECT * FROM [MySheet$A1:B4];

or with the sheet name omitted e.g.

SELECT * FROM [A:B];

in which case the worksheet at position Worksheets(1) will be used
regardless of visibility.

Jamie.

--