Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: issue with office 2003 and copyFromRecordset

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default issue with office 2003 and copyFromRecordset

    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.

  2. #2
    Board Regular
    Join Date
    Dec 2004
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

    Code:
        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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •