BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets

If you run the OpenSchema(adSchemaTables) method of an ActiveX Data Objects (ADO) Connection object against an Excel 2000 database/workbook (.xls file) with the Microsoft OLE DB Provider for Jet 4.0, the TABLE_TYPE for Worksheets in the workbook return as "TABLE" instead of as "SYSTEM TABLE". The expected behavior is to that the TABLE_TYPE for User-defined named ranges return as "TABLE", and the TABLE_TYPE for Worksheets in the .xls file return as "SYSTEM TABLE".

As a result, applications that use ADO to retrieve table metadata information that is required to identify the user-defined named ranges in an Excel workbook cannot distinguish between named ranges and worksheets when they use OLE DB Provider for Jet 4.0.

To work around this problem, use the Excel ODBC driver to retrieve metadata information that is required to distinguish User-defined Ranges from Worksheets in the workbook. The Excel ODBC driver correctly returns the TABLE_TYPE for Worksheets as "SYSTEM TABLE".

Microsoft has confirmed that this is a bug in Microsoft OLE DB Provider for Jet, version 4.0.

Steps to Reproduce Behavior

1. Create an empty Excel 2000 workbook named Repro.xls, and save it to disk in the root folder of drive C.
2. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
3. Add a CommandButton control to Form1.
4. Copy and paste the following Visual Basic code in the Click event procedure of the CommandButton to run the ADO code that is required to access metadata information about the tables in Repro.xls:

   Dim cn As ADODB.Connection
   Dim schemaRS As ADODB.Recordset
   
   Set cn = New ADODB.Connection
      
   'cn.Provider = "Microsoft.Jet.OLEDB.4.0"
   'cn.ConnectionString = "Data Source=c:\repro.xls;Extended Properties=Excel 8.0"
   
   cn.Provider = "MSDASQL"
   cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)}; & _
      "DBQ=c:\repro.xls;ReadOnly=False;"
       
   cn.Open
   Set schemaRS = cn.OpenSchema(adSchemaTables)
   
   Do While Not schemaRS.EOF
      Debug.Print schemaRS.Fields("TABLE_NAME") & " : " & schemaRS.Fields("TABLE_TYPE")
      schemaRS.MoveNext
   Loop
   
   schemaRS.Close
   Set schemaRS = Nothing
   
   cn.Close
   Set cn = Nothing 
   						

   NOTE: The preceding code uses the Excel ODBC driver to execute the OpenSchema method and access the table metadata information.
5. Save and run the project. When you use the ODBC driver, notice that the TABLE_TYPE of the Worksheets is returned as "SYSTEM TABLE".
6. Comment out the code that uses the ODBC driver to open the connection, and uncomment the lines that specify the Jet OLE DB 4.0 Provider. When you run the code, notice that the TABLE_TYPE for Worksheets is now returned as "TABLE".