Recently I ran into error number ‘-2147467259‘ with description “Method ‘CopyFromRecordset’ of object ‘Range’ failed” while transferring a data set from SQL Server into Excel using the aforementioned method on an ADODB.RecordSet.
Of course I Googled for the error but the answers were vague and often pointed to Null values in the RecordSet’s data, but I had other sets that had such empty values and these were dropped without problems, so it had to be somewhere else, in particularly in the data.
The reason for this error is not so easy to find, since CopyFromRecordset may indeed copy data to the worksheet (in my case 31,655 rows of data were copied out of 41,899). From Excel’s point of view, the RecordSet is like a huge array and CopyFromRecordset drops the values by columns and by rows (“Over, then down” in Excel terms). At row 31,655 data was dropped in columns 1 til 4 but no data from column 5 til the last column (31).
After investigation I have been able to isolate the problem and discovered that a very long string in column 5 of the RecordSet —retrieved from a MEMO data type— generated the error. The offending string was 10366 characters long, about a third of Excel’s maximum cell size of 32767 characters. I also discovered that other records having very large text —about 8100 characters— in the same field in the RecordSet were copied to the worksheet successfully. This made me curious.
I prepared a small test using Excel and Access (2007) and finally was able to establish the upper limit at 8203 character that CopyFromRecordset could handle without problems. Any string length above 8203 characters inevitably fails.
Note: this error is likely generated only when there are at least 2 rows or columns in the RecordSet. A RecordSet with a single value may not even trigger it, or at least I was unable to.
If you have control on the way the RecordSet is generated and you want to use CopyFromRecordset then you should make sure no strings are retrieved large than 8203 characters. If not, then you should revert to use the GetRows method from ADODB and walk your way through the array chopping off any excess characters from the string values before dropping the array to the Range, otherwise this will fail also.
Dutch Gemini's Weblog Pages 
Well spotted. I had the same problem in Excel 2007 and I had a feeling that there was a limit on the number of characters. The record I got this error had a note field with 8204 characters. No where this bug is documented but nice to see an explanation here.
By the way I tested the same code in Excel 2010 and I didn’t get this error. It must have been increased or fixed in this version!
Comment by Edgar Badawy — June 20, 2012 @ 1:13 pm |
Thank you for posting this very helpful information!
Comment by DMB — October 3, 2012 @ 4:47 pm |
Thanks so much ! really helpful
Comment by CM — November 22, 2012 @ 6:53 pm |
Thanks, Dutch. I’m having this problem in Access 2007 with no memo fields. Using the same procedure with a DAO recordset causes no problem and displays the results just fine (pulling the same data from a different table in SQL Server, so there apparently some kind of differenct). Further, the exact same code was working with ADO previously without a hitch, and using the same size data fields from the same table. ???
Comment by Dan Murff — March 29, 2013 @ 4:09 pm |
The same experiment based on an array created in Excel thus without ADO or any other method for creating RecordSets gave the same error, so IMHO the issue generates all and only in Excel, maybe after a particular Windows or Office update. In one of the comments Excel 2010 was believed to have solved this problem.
Comment by dutchgemini — April 2, 2013 @ 8:14 am |
It turned out my problem was using a where clause in my query on a linked SQL server 2008 table that was too large for access to handle (5+ million records). Apparently Access loads the entire table in memory before processing the filter. Querying that table without the where clause worked.
Comment by Dan Murff — April 2, 2013 @ 1:28 pm
Thx … that’s for the “records”.
Comment by dutchgemini — April 2, 2013 @ 2:36 pm
Oops, the table was only 170,000 records. It was loaded with a stored procedure from the 5M record table. Still, Access failed with the where clause on that small a table.
Comment by Dan Murff — April 2, 2013 @ 2:45 pm
I had the same problem but was able to solve it:
Sub ConnectDB()
Dim db As Database
Dim rst As Recordset
Dim i As Long
Set db = OpenDatabase(“C:\MyDatabase.mdb”)
Set rst = db.OpenRecordset(“MyTable”)
On Error GoTo large_text
Cells.Clear
Range(“A2”).Select
‘Dumps the database to excel
Do Until rst.EOF
For x = 0 To 56 ‘I only have 56 fields in my database
ActiveCell.Offset(0, x) = rst.Fields(x)
Next x
Rows(ActiveCell.Row).RowHeight = 12 ‘ what can I say … I like my rows to be the same height!
ActiveCell.Offset(1, 0).Select
rst.MoveNext
Loop
‘close the objects
rst.Close
db.Close
‘destroy the variables
Set rst = Nothing
Set db = Nothing
Exit Sub
large_text:
ActiveCell.Offset(0, x) = Left(rst.Fields(x), 60000) ‘allows text > 3803 to go into the cell
ActiveCell.Offset(0, x).Interior.ColorIndex = 35 ‘added color to notice the cell more easily
Err.Clear
Debug.Print x, ActiveCell.Row
Resume Next
End Sub
Comment by Mike — April 4, 2013 @ 2:49 pm |
If I understand well you are actually dropping a RecordSet cell-by-cell. Apart from being CPU-intensive, this veers off the initial topic.
The CopyFromRecordset() method drops the entire RecordSet on a sheet in a workbook, working left-to-right and top-to-bottom. This method is very performing, since it requires 1 single statement and is written in high-speed language.
Before reverting to cell-by-cell dropping of data you can use column-by-column dropping. You do this by extracting each column (i.e. the ‘field’) using the GetRows(). GetRows() returns an array (which much be ‘transposed’ since rows and columns are flipped) that you can drop into a range of cells. This method is slower than CopyFromRecordset() because it requires a loop (in VBA) walking through the number of fields in your RecordSet but still definitively faster than cell-by-cell.
Nevertheless, using column-by-column dropping may still yield errors if one of the elements in the array contains offending data. As I mentioned, for Excel a RecordSet is technically equal to an array, and using CopyFromRecordset() or an array obtained from GetRows() will eventually trigger errors.
The cell-by-cell dropping of data should always be used as the last option, executing a total number of VBA statements even greater than the Cartesian product of (rows*columns) with some additional overhead for the loop structure. What I have seen so far, writing cell-by-cell is not affected by the 8203 character limit.
I would go for CopyFromRecordset() first, then fail over to column-by-column using GetRows() and finally go cell-by-cell on the columns that were rejected in the previous 2 attempts.
Note: Excel may gracefully ignore invalid data (such as a ’01/01/1800′ date) when dropping using CopyFromRecordset() or the array obtained from GetRows() but this invalid data is absolutely not accepted in cell-by-cell approach (e.g. error 1004)
Comment by dutchgemini — April 5, 2013 @ 9:47 am |
Thank you! This solved my problem.
Comment by jeffshieldsdevj — March 26, 2014 @ 2:49 pm |
Thanks for the post. It solved my problem!
Comment by Sherry Xie — December 15, 2014 @ 7:39 pm |
Thank you for the post!
I also had this problem with excel 2003 (… 2003!?!?!…..yes, some of my customers still use!),
To excel 2003 the ‘limit’ is 910 characters, any string length above 910 characters will fail.
I was copying an entire range to the excel:
object[,] rawData = new object[iDtPlan.Rows.Count, iDtPlan.Columns.Count];
…
…
iRange.Value2 = rawData; //<-here occurs the Error -2147467259 when had 'big values'
So, to solve, I put the values with more than 8203 characters (910 characters to excel 2003) in another list and after copy the entire range I used a 'foreach' in the list of the 'big characters', defining only one cell in range, using the property 'Value' not 'Value2':
Range workingRangeCells = excelSheet.get_Range(item.PosLetter + item.posCol);
workingRangeCells.Value = item.ValBig;
Comment by Erivelton Rosante — February 19, 2015 @ 6:34 pm |
THANK YOU VERY MUCH this article helped me a lot in troubleshooting a problem what I was stuck on for weeks.
Comment by Yousuf — March 16, 2015 @ 2:22 pm |
[…] Error occurs in Memo field over 8000 chars (ref: https://dutchgemini.wordpress.com/2012/03/21/error-2147467259-method-copyfromrecordset-of-object-rang…😉 […]
Pingback by Export MySQL data to MS Excel files using VBA | SIth — July 31, 2015 @ 4:48 pm |
[…] Error occurs in Memo field over 8000 chars (ref: https://dutchgemini.wordpress.com/2012/03/21/error-2147467259-method-copyfromrecordset-of-object-rang…) […]
Pingback by Export MySQL data to MS Excel files using VBA | SIth — August 1, 2015 @ 1:21 pm |
Thank you.
Comment by Don — September 29, 2015 @ 12:00 am |