Word 2k3; ADO to XLS - field too small error

[MakeItSo]

Hi friends,

once again it is a little thing that gets me stumped.
I am trying to read data from an XLS into Word.
I am doing so via ADODB; the reason for this is simple: it is a mere test, I will later translate to VBScript and use it in an ASP to write to a web page.

Connection functions, first record is read, but then an error occurs "field too small for the amount of data", and it occurs at the line "rs.MoveNext".

What I do not understand: I am not manipulating the recordset at all, I am only reading it!

Here's my code:

Dim strConnection As String, conn As ADODB.Connection, rs As ADODB.Recordset, strSQL As String
Dim i  As Integer

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pathtomyexcel.xls;" & _
    "Extended Properties=""Excel 8.0;HDR=Yes;"";"

Set conn = New ADODB.Connection
conn.Open strConnection

Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM [Tabelle1$]"
rs.Open strSQL, conn, adOpenDynamic,[b] adLockReadOnly[/b]

Application.Documents.Add

If Not rs.EOF Then
    For i = 0 To rs.Fields.Count - 1
        Selection.TypeText rs.Fields(i).Name & vbTab
    Next i
End If
Selection.TypeParagraph
Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 1
        Selection.TypeText rs.Fields(i).Value & vbTab
    Next i
    Selection.TypeParagraph
[b]    rs.MoveNext[/b]
Loop

As you can see, I am doing nothing to the excel file, I am opening the connection readonly, yet the code fails at the MoveNext.

Can you tell me what I'm doing wrong?

Does it have anything to do with my first loop?
As you can see it is just there to get the column headings.

It writes them all neatly, as well as the values of the first record, but then it fails.

Thanks a lot!
Andy

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Typical.
I post once, rant once and then find the solution:

I must NOT open ReadOnly but adLockOptimistic! Then it works!

Just.... WHY?


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell