issue with office 2003 and copyFromRecordset

[deadseasquirrels]

So using recordsets and connections to databases were all working fine until my computer got upgraded to Office 2003 while I was out on jury duty. Now I'm back and my queries won't work. But i figured it out. I have a query tha pulls about 20 columns of data, the 20th column is a comments field (I am pulling from an Oracle DB by the way), so typically it is a huge string. Everytime I pull my data and use the copyFromRecordset command excel throws an error saying

Run-time error '-2147467259 (80004005)':
Method 'CopyFromRecordset' of object 'Range' Failed

Then I deleted the comments field from my db query and tried again, and everything works fine. Now my question is, does anybody have any idea what kind of information could possibly be in the comments field (other than it being really long) that could make CopyFromRecordset fail for Excel 2003 but not for Excel XP? If it is just a matter of length, I would be shocked because i would think an upgrad would upgrade a cell's capacity.

 

[deadseasquirrels]

not that I've solved this problem, but I've did a little more digging and it seems like this is a new problem with Excel 2003, where you can't import through OLEDB a field that is larger than 256 words long or something like that, but that the limitation for a cell is still larger than that. So you can manually input a string that is 500 words, but you can't automatically have it imported using OLEDB.

However one work around that has worked for me is doing something like this:

    Dim i As Integer
    Dim tempString As String
    i = 1
    Do While Not importsRS.EOF
        tempString = importsRS.Fields(0)
        Cells(i, 31) = tempString
        importsRS.MoveNext
        i = i + 1
    Loop

basically assign the recordset field to a string and then assign the cell to the string. It's kind of kludgy but I don't see another way.