Microsoft KB Archive/314763

= FIX: ADO Inserts Data into Wrong Columns in Excel =

Article ID: 314763

Article Last Modified on 9/26/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft OLE DB Provider for Jet 4.0

-



This article was previously published under Q314763



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 BUG: Memory Leak When You Query Open Excel Worksheet with ADO

Refer to the &quot;More Information&quot; 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 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.



Steps to Reproduce Behavior
 Open Microsoft Excel, and then create a new workbook. In Sheet1, type the following sample data, beginning with cell A1 in the upper left corner:

 Save the workbook as Test.xls. You can leave the Excel application open, but you must close the new workbook. In Microsoft Visual Basic, create a new Standard EXE project. Form1 is created by default. On the Project menu, click References. From the list of available references, select Microsoft ActiveX Data Objects 2.x Library.  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 = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=&quot; & App.Path & &quot;\Test.xls;&quot; & _ &quot;Extended Properties=Excel 8.0&quot; Set cn = New ADODB.Connection cn.Open strCn 'Add new values. Set rs = New ADODB.Recordset With rs     .CursorLocation = adUseClient .Open &quot;Select * from [Sheet1$]&quot;, cn, adOpenStatic, adLockOptimistic .AddNew .Fields(&quot;ColumnA&quot;).Value = 3 .Fields(&quot;ColumnB&quot;).Value = 3 .Fields(&quot;ColumnC&quot;).Value = &quot;&quot; .Fields(&quot;ColumnD&quot;).Value = 3 .Fields(&quot;ColumnE&quot;).Value = 3 .Fields(&quot;ColumnF&quot;).Value = &quot;testing&quot; .Update .Close End With Set rs = Nothing cn.Close Set cn = Nothing End Sub </li> Save your Visual Basic test project in the same folder as your workbook, Test.xls.</li> 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 Collating Sequence Error Opening ADODB Recordset the First Time Against an Excel XLS

</li> 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.</li> Close the form to end the project. Reopen Test.xls in Excel, and then examine the data in Sheet1. You expect the following results:

However, you see the following data:

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.</li></ol>

Variation 1
Configure the test data as follows:

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 &quot;Select * from [Sheet1$]&quot;, cn, adOpenStatic, adLockOptimistic .AddNew .Fields(&quot;ColumnA&quot;).Value = 3 .Fields(&quot;ColumnB&quot;).Value = &quot;&quot; .Fields(&quot;ColumnC&quot;).Value = 3 .Fields(&quot;ColumnD&quot;).Value = 3 .Fields(&quot;ColumnE&quot;).Value = &quot;testing&quot; .Update .Close End With Set rs = Nothing cn.Close Set cn = Nothing When you open Test.xls in Excel, Sheet1 displays the following data:

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:

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 &quot;Select * from [Sheet1$]&quot;, cn, adOpenStatic, adLockOptimistic .AddNew .Fields(&quot;ColumnA&quot;).Value = 3 .Fields(&quot;ColumnB&quot;).Value = 3 .Fields(&quot;ColumnC&quot;).Value = &quot;&quot; .Fields(&quot;ColumnD&quot;).Value = &quot;&quot; .Fields(&quot;ColumnE&quot;).Value = 3 .Fields(&quot;ColumnF&quot;).Value = 3 .Fields(&quot;ColumnG&quot;).Value = &quot;testing&quot; .Update .Close End With Set rs = Nothing cn.Close Set cn = Nothing When you open Test.xls in Excel, Sheet1 displays the following data:

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.

<div class="references_section">