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 "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 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
- 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:
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF 1 1 testing 1 1 testing 2 2 testing 2 2 testing
- 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 = "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- Save your Visual Basic test project in the same folder as your workbook, Test.xls.
- 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:
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
- 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.
- Close the form to end the project. Reopen Test.xls in Excel, and then examine the data in Sheet1. You expect the following results:
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF 1 1 testing 1 1 testing 2 2 testing 2 2 testing 3 3 3 3 testing
However, you see the following data:
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF 1 1 testing 1 1 testing 2 2 testing 2 2 testing 3 3 3 3 testing
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:
| ColumnA | ColumnB | ColumnC | ColumnD | ColumnE |
|---|---|---|---|---|
| 1 | testing | 1 | 1 | testing |
| 2 | testing | 2 | 2 | testing |
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:
| ColumnA | ColumnB | ColumnC | ColumnD | ColumnE |
|---|---|---|---|---|
| 1 | testing | 1 | 1 | testing |
| 2 | testing | 2 | 2 | testing |
| 3 | 3 | 3 | testing |
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:
| ColumnA | ColumnB | ColumnC | ColumnD | ColumnE | ColumnF | ColumnG |
|---|---|---|---|---|---|---|
| 1 | 1 | testing | testing | 1 | 1 | testing |
| 2 | 2 | testing | testing | 2 | 2 | testing |
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:
| ColumnA | ColumnB | ColumnC | ColumnD | ColumnE | ColumnF | ColumnG |
|---|---|---|---|---|---|---|
| 1 | 1 | testing | testing | 1 | 1 | testing |
| 2 | 2 | testing | testing | 2 | 2 | testing |
| 3 | 3 | 3 | 3 | testing |
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 ACC2002: Nulls Replaced with Next Field's Data When You Export to Excel
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Keywords: kbbug kbfix kbqfe kbiisam kbjet kbhotfixserver KB314763
