BUG: Excel ODBC Driver Disregards the FirstRowHasNames or Header Setting

Article translations Article translations
Article ID: 288343 - View products that this article applies to.
This article was previously published under Q288343
Expand all | Collapse all

On This Page

SYMPTOMS

When you use the Microsoft Excel Open Database Connectivity (ODBC) Driver to connect to an Excel worksheet as an ODBC data source, the driver assumes by default that the first row of worksheet data contains column headers (field names). According to the driver documentation, the optional FirstRowHasNames connection setting can be used to change this default behavior by using 0 for False and 1 for True. However, in fact, the driver disregards this setting and the first row of data is always treated as column headings. If the first row of data does not contain column headings or other entries, the effective result is that the first row of data "disappears."

CAUSE

The value of the FirstRowHasNames or Header (HDR) parameter is never being passed to the Excel ODBC Driver.

RESOLUTION

The HDR= setting is available and functional when you use the Microsoft Jet OLE DB Provider and its Excel ISAM driver. Therefore, the best solution is to use the Jet Provider rather than the Excel ODBC driver.

Because the Excel ODBC driver always assumes that the first row contains field names, the first row must always contain either valid field names, or dummy entries that the developer is willing to disregard.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a new Excel workbook and save it with the default name in the folder where you will create your Visual Basic test project.
  2. In cell A1, fill a few cells in the first column with random data.
  3. Create a new Visual Basic project and set a reference to ActiveX Data Objects (ADO).
  4. Enter and run the following code:
    Private Sub Form_Load()
      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset
      Set cn = New ADODB.Connection
      With cn
        .Provider = "MSDASQL"
        .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & App.Path & "\Book1.xls;FirstRowHasNames=0;"
        .Open
      End With
      Set rs = New ADODB.Recordset
      With rs
        Set .ActiveConnection = cn
        .Source = "[Sheet1$]"
        .Open
      End With
      Debug.Print rs.Fields(0).Value
      Debug.Print rs.Fields(0).Name
      rs.Close
      cn.Close
    End Sub
    					
Normally you would expect to see the Excel driver assign the default field name "F1" to your column of data and to display the data beginning with the first record that you entered in cell A1. However, note that in the Debug window, the value that you entered in cell A2 printed as the value of the first record in spite of your having set FirstRowHasNames to False. The value in cell A1 has "disappeared" to become the field name for the column.

Properties

Article ID: 288343 - Last Review: September 16, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.1 Service Pack 1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5 Service Pack 1
  • Microsoft Data Access Components 2.5
Keywords: 
kbbug kbmdacnosweep kbpending KB288343

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com