I am using Access XP SP3 and am having some issues with the CopyFromRecordset to insert data into Excel. In searching this forum and others, I see that people are also having problems with this function and getting correct results out of it. I was getting the following run-time error:

-2147467259 (80004005). Method 'CopyFromRecordset' on object 'Range' Failed.

The recordset that I was dumping out had a couple of Memo fields, and one of the fields happend to have over 2k characters in it. After trimming it down a bit, I found out that the method _would_ work when the field in question was 910 characters and below. I tried this out on another machine that had Office 2000 SP1 on it and the limit jumped to 1823 characters. The Office 2k machine would just truncate the rest of the characters and not give me a runtime error.

Out of curiosity, I tried to assign the contents of the memo field to a variable and then dump the variable out to the spreadsheet (on the XP SP3 machine). This worked and even included all chacters (all 2100 of them). For example

foo = rstcheck![Field Memo]
objsheet2.cells(4,2).value = foo

This would dump the entire Memo contents out to the spreadsheet, when I should be able accomplish this with the 'copyfromrecordset' Method.

Questions are as follows:

-Why would the copyfromrecordset method fail when I can just assign the memo field to a variable and dump it out to the spreadsheet, as in the example above? I would rather not do things this way as this would invite a bit more code when the original method should work.

-Why the character difference between the Office XP and Office 2k machines vary so much (and by a multiple of 2)?

-Does anyone have an idea of what is going on with the 'copyfromrecordset' method between Office releases?