Microsoft KB Archive/300948

= BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets =

Article ID: 300948

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Excel 2000 Service Pack 1
 * Microsoft Excel 2000 Standard Edition
 * Microsoft OLE DB Provider for Jet 4.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Data Access Components 2.5 Service Pack 2
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.6 Service Pack 1
 * Microsoft Excel 2002 Standard Edition

-



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 &quot;TABLE&quot; instead of as &quot;SYSTEM TABLE&quot;. The expected behavior is to that the TABLE_TYPE for User-defined named ranges return as &quot;TABLE&quot;, and the TABLE_TYPE for Worksheets in the .xls file return as &quot;SYSTEM TABLE&quot;.

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 &quot;SYSTEM TABLE&quot;.



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



Steps to Reproduce Behavior
 Create an empty Excel 2000 workbook named Repro.xls, and save it to disk in the root folder of drive C. Create a new Standard EXE project in Visual Basic. Form1 is created by default. Add a CommandButton control to Form1.  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 = &quot;Microsoft.Jet.OLEDB.4.0&quot; 'cn.ConnectionString = &quot;Data Source=c:\repro.xls;Extended Properties=Excel 8.0&quot;

cn.Provider = &quot;MSDASQL&quot; cn.ConnectionString = &quot;Driver={Microsoft Excel Driver (*.xls)}; & _ &quot;DBQ=c:\repro.xls;ReadOnly=False;&quot; cn.Open Set schemaRS = cn.OpenSchema(adSchemaTables)

Do While Not schemaRS.EOF Debug.Print schemaRS.Fields(&quot;TABLE_NAME&quot;) & &quot; : &quot; & schemaRS.Fields(&quot;TABLE_TYPE&quot;) 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.  Save and run the project. When you use the ODBC driver, notice that the TABLE_TYPE of the Worksheets is returned as &quot;SYSTEM TABLE&quot;. 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 &quot;TABLE&quot;.</li></ol>

Keywords: kbbug kbpending KB300948

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.