Error - Method 'CopyFromRecordset' of object 'Range' failed

[marko]

I am trying to retrieve data from a query in an Access 97 database and
populate Sheets in an Excel 2002 workbook using 'CopyFromRecorset'.

The problem i am having is that 2 out of the 7 Sheets (all sheets are being
populated one after the other within the same piece of code) are creating the
'Method 'CopyFromRecordset' of object 'Range' failed' error message and i
can't understand why? (If it was something to do with my programming sure all
sheets would fail!!)

Has anyone else come across this problem? If so, is there an answer to it?

Any help will be greatly appreciated

Mark

 

[Sam Wilson]

A few things you can try:

1. Is it possible that the recordest is empty?
2. Is it possible that the recordset contains >65536 records?
3. Are any of the fields in the recordset strings, and if so are they > 255
characters long?

 

[marko]

Thanks for the reply Sam.

Unfortunately all the suggestion below are covered.

There seems to be no logic as such behind why this happens.
The sheet that is populated prior to the failure returns 37k rows, the sheet
the error appears on still gets populated after Oking the error but with less
records than expected (4k rows & should return 24k rows).

 

[Sam Wilson]

If it only populates 4000 rows then there must be something on row 4001 that
it doesn't like.

You may have to compare the results in the query in Access to find out what
the next record should be. I still thing it might be a string of length > 255
characters, in which case you'll need to make that field the last one
returned for each record.

 

[marko]

Thanks again Sam,

Took you advise and checked the query and it turns out that there is a
Divide By 0 Error on that column.