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?

-Would the references that I have set in VBA have anything to do with this? (I have the latest ones selected)

-Should I have posted this in the Automation area?

Edited by: Juicer747 on Tue Mar 1 9:27:51 EST 2005.

I think this is related to some of the patches that are pushed to the machine by the Air Force (security, etc.). Has anyone had any issues in a military/corporate environment where things 'broke' after a patch was pushed to their machine by their IT department? I was able to get the same results with a Office XP SP2/SP1 machine as with the Office 2000 machine, but when i updated the computer with a few IT patches, the problem cropped back up again and I started to get run-time errors.

I hope I am explaining my problem adequately.