Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets

View products that this article applies to.

This article was previously published under Q300948

↑ Back to the top


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.

↑ Back to the top


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

↑ Back to the top


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

↑ Back to the top

More information

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)}; & _
    Set schemaRS = cn.OpenSchema(adSchemaTables)
    Do While Not schemaRS.EOF
       Debug.Print schemaRS.Fields("TABLE_NAME") & " : " & schemaRS.Fields("TABLE_TYPE")
    Set schemaRS = Nothing
    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".

↑ Back to the top

Keywords: kbbug, kbpending, KB300948

↑ Back to the top

Article Info
Article ID : 300948
Revision : 5
Created on : 2/12/2007
Published on : 2/12/2007
Exists online : False
Views : 400