Range().copyfromRecordset not working for one recordset

[deadseasquirrels]

Let me describe my problem. Within Excel I hit an oracle database and retreive some data and then I do a range("A1").copyFromRecordset dbRS, where dbRS is the recordset. I've done this a million times and it works fine. Then I decided to do the same thing for one query that was a little more complex, but it throws a very generic " " error

So I figure, something is wrong with the recordset, but not so. When I do this:

Do While Not dbRS.EOF
    Debug.Print (dbRS.Fields(1) & " " & dbRS.Fields(4) & " " & dbRS.Fields(5))
    dbRS.MoveNext
Loop

It prints out all the fields fine. I also decide that maybe it's the SQL statement, so I output the SQL statement to Golden (an Oracle client) and the query runs fine as well.

The error I get is the following:

There was an error Method 'CopyFromRecordset' of object 'Range' failed

So basically I was wondering if anybody can think of any reason why this recordset isn't working with the CopyFromRecorset function or if there is more I can do to troubleshoot. I can't show most of my code because there is a lot of private stuff in there, and I'm not sure how helpful it would be either. Please help if you can. Thank you.

[RobDog888]

If the Recordset object contains fields with OLE objects in them then it will give that error.
Also, only works with ADO or DAO rs'

[si_the_geek]

I could be wrong, but I think you also get that error if you have more than 255 columns or 65536 records.

[deadseasquirrels]

I am positive the latter dimensional problems is not the issue because I ran the sql query in another client and it is well within range. As for the OLE objects...how does one get OLE objects into a recordset? I am really just running a SQL query on an oracle database pulling up text.

Also let me also mention that everything was working fine, even this macro that I am describing right now before my excel was changed from Office XP to Office 2003. In addition to the strangeness all my other scripts run fine, it is really just this one. I am so lost.

Since my first post I've tried outputting the recordset to a text file by going through each field like

while not dbRS.eof do
For i = 1 to 31
[output to file]-> dbRS.fields(i)
next i
loop

or something like that and that seems to work too.

[si_the_geek]

I'm not sure what data types Oracle has, but are any of the data types for those fields something like "Memo" (or other "very long" text) or a custom data type?
These will typically be seen as OLE objects within a recordset.

If there isn't anything obvious, try removing the fields one at a time to see if you can find which one is causing the problem.

Are you using ADO or DAO to get your recordset? Whichever you are using, it may be worth trying the other.

[deadseasquirrels]

hmm, interesting that you mention that there is one particularly large field that is like a memo/notes. I'll try removing it and see what happens. The thing is though, lets say that field is field 5. If I do

do while not dbRS.EOF
    msgbox dbRS.fields(5)
    dbRS.movenext
loop

it comes out fine and it prints the field out fine. I hate to say this, because i hate it when others say it since it really doesn't help much, but the damn macro was working fine just a few days ago, and coincidentally I was out on jury duty those few days and my office got upgraded to 2003, and now it doesn't work. It's a strange thing. I am using ADO by the way, I'm afraid of moving to DAO, I always thought ADO was the way to go.

[si_the_geek]

You can use OLE fields without a problem in VB/VBA, it is CopyFromRecordset that can't work with them. There may be some conversion that can be done on the problem field(s) to make them non-OLE.

I don't know why it would have "broken" by upgrading to Office 2003, I never had any issues with it (including CopyFromRecordset in several programs).

ADO is certainly preferable, but DAO may work... just offering an alternative in case we can't find another solution

[RobDog888]

Taking out the memo field is a good test. There may also be a unsupported or invalid character in the field that is causing the issue too?