DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 314763 - Last Review: June 19, 2014 - Revision: 2.0

Hotfix Download Available
View and request hotfix downloads
 
 

On This Page

Symptoms

When you use ADO to insert new rows of data into a Microsoft Excel worksheet, if the data includes fields that contain empty string values, ADO may insert the data values of subsequent numeric fields into the wrong columns in Excel.

This problem occurs in both Microsoft OLE DB Provider for Jet version 4.0 and Microsoft ODBC Driver for Excel. This problem occurs whether you use a SQL INSERT statement or the AddNew and the Update methods of the ADO Recordset object.

This problem does not occur if the Excel workbook is open in the Excel application when ADO inserts the new records.

However, Microsoft discourages this practice because a memory leak occurs if Excel is open during ADO operations. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
319998  (http://support.microsoft.com/kb/319998/EN-US/ ) BUG: Memory Leak When You Query Open Excel Worksheet with ADO
Refer to the "More Information" section for details about the circumstances in which this problem occurs.

Resolution

To resolve this problem, obtain the latest service pack for Jet 4.0 service pack. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
239114  (http://support.microsoft.com/kb/239114/ ) How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine

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. Open Microsoft Excel, and then create a new workbook.
  2. In Sheet1, type the following sample data, beginning with cell A1 in the upper left corner:
    Collapse this tableExpand this table
    ColumnAColumnBColumnCColumnDColumnEColumnF
    11testing11testing
    22testing22testing

  3. Save the workbook as Test.xls. You can leave the Excel application open, but you must close the new workbook.
  4. In Microsoft Visual Basic, create a new Standard EXE project. Form1 is created by default.
  5. On the Project menu, click References. From the list of available references, select Microsoft ActiveX Data Objects 2.x Library.
  6. Place a CommandButton control on Form1, and then paste the following code in the Click event procedure for the button. Note that this code inserts an empty string into the middle column, Column C.
    Private Sub Command1_Click()
       Dim strCn As String
       Dim cn As ADODB.Connection
       Dim rs As ADODB.Recordset
       Dim fld As ADODB.Field
    
       'Open connection
       strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\Test.xls;" & _
          "Extended Properties=Excel 8.0"
       Set cn = New ADODB.Connection
       cn.Open strCn
        
       'Add new values.
       Set rs = New ADODB.Recordset
       With rs
          .CursorLocation = adUseClient
          .Open "Select * from [Sheet1$]", cn, adOpenStatic, adLockOptimistic
          .AddNew
          .Fields("ColumnA").Value = 3
          .Fields("ColumnB").Value = 3
          .Fields("ColumnC").Value = ""
          .Fields("ColumnD").Value = 3
          .Fields("ColumnE").Value = 3
          .Fields("ColumnF").Value = "testing"
          .Update
          .Close
       End With
       Set rs = Nothing
       cn.Close
       Set cn = Nothing
    End Sub
    					
  7. Save your Visual Basic test project in the same folder as your workbook, Test.xls.
  8. Run the project, and then click the button. The first time you run the project in the Visual Basic Integrated Development Environment (IDE), you may receive the following error message:
    Runtime error '-2147467259 (80004005)': Selected collating sequence not supported by the operating system
    This is a known issue.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
    246167  (http://support.microsoft.com/kb/246167/EN-US/ ) Collating Sequence Error Opening ADODB Recordset the First Time Against an Excel XLS
  9. In the error message dialog box, click Debug, and then press the F5 key to continue to run the project. Note that this inserts two new rows of data instead of one because Update is run twice.
  10. Close the form to end the project. Reopen Test.xls in Excel, and then examine the data in Sheet1. You expect the following results:

    Collapse this tableExpand this table
    ColumnAColumnBColumnCColumnDColumnEColumnF
    11testing11testing
    22testing22testing
    3333testing

    However, you see the following data:

    Collapse this tableExpand this table
    ColumnAColumnBColumnCColumnDColumnEColumnF
    11testing11testing
    22testing22testing
    3333testing

    It appears as if the empty string that is inserted into ColumnC has been ignored and has disappeared. Therefore, any subsequent numeric values are inserted one column to the left of their intended destinations. The subsequent string column is not affected.

Variation 1

Configure the test data as follows:

Collapse this tableExpand this table
ColumnAColumnBColumnCColumnDColumnE
1testing11testing
2testing22testing

In the Visual Basic project, modify the section to add new values as follows:
   'Add new values.
   Set rs = New ADODB.Recordset
   With rs
      .CursorLocation = adUseClient
      .Open "Select * from [Sheet1$]", cn, adOpenStatic, adLockOptimistic
      .AddNew
      .Fields("ColumnA").Value = 3
      .Fields("ColumnB").Value = ""
      .Fields("ColumnC").Value = 3
      .Fields("ColumnD").Value = 3
      .Fields("ColumnE").Value = "testing"
      .Update
      .Close
   End With
   Set rs = Nothing
   cn.Close
   Set cn = Nothing
				
When you open Test.xls in Excel, Sheet1 displays the following data:

Collapse this tableExpand this table
ColumnAColumnBColumnCColumnDColumnE
1testing11testing
2testing22testing
333testing

Notice that this problem does not occur when a single numeric column precedes the empty string value.

Variation 2

Configure the test data as follows:

Collapse this tableExpand this table
ColumnAColumnBColumnCColumnDColumnEColumnFColumnG
11testingtesting11testing
22testingtesting22testing

In the Visual Basic project, modify the section to add new values as follows:
   'Add new values.
   Set rs = New ADODB.Recordset
   With rs
      .CursorLocation = adUseClient
      .Open "Select * from [Sheet1$]", cn, adOpenStatic, adLockOptimistic
      .AddNew
      .Fields("ColumnA").Value = 3
      .Fields("ColumnB").Value = 3
      .Fields("ColumnC").Value = ""
      .Fields("ColumnD").Value = ""
      .Fields("ColumnE").Value = 3
      .Fields("ColumnF").Value = 3
      .Fields("ColumnG").Value = "testing"
      .Update
      .Close
   End With
   Set rs = Nothing
   cn.Close
   Set cn = Nothing
				
When you open Test.xls in Excel, Sheet1 displays the following data:

Collapse this tableExpand this table
ColumnAColumnBColumnCColumnDColumnEColumnFColumnG
11testingtesting11testing
22testingtesting22testing
3333testing

If ADO inserts two empty string values, it appears as if the empty strings that are inserted into ColumnC and ColumnD are ignored and have disappeared. Therefore, any subsequent numeric values are inserted two columns to the left of their intended destinations. The subsequent string column is not affected.

References

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
294410  (http://support.microsoft.com/kb/294410/EN-US/ ) ACC2002: Nulls Replaced with Next Field's Data When You Export to Excel
257819  (http://support.microsoft.com/kb/257819/EN-US/ ) HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Keywords: 
kbhotfixserver kbqfe kbbug kbfix kbiisam kbjet KB314763
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support