BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets (300948)


The information in this article applies to:

  • Microsoft Excel 2000 Service Release 1 (SR-1)
  • Microsoft Excel 2000
  • Microsoft OLE DB Provider for Jet 4.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 SP1
  • Microsoft Data Access Components 2.1 SP2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 SP1
  • Microsoft Data Access Components 2.5 SP2
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.6 SP1
  • Microsoft Excel 2002
This article was previously published under Q300948

SYMPTOMS

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.

RESOLUTION

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

STATUS

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

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)}; & _
   "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".
Modification Type:MajorLast Reviewed:12/3/2003
Keywords:kbbug kbpending KB300948
©2003 Microsoft Corporation. All rights reserved.