Error -2147417856 (80010100)

[CPGDeveloper]

Hello~

I am working on an application (MS Access Front End/Azure SQL Back End). I have a reporting tool that runs 10+ Reports at once, looping through each one, and exporting it to Excel. It generally works fine, however occasionally it gets stuck on the following line:

osheet.Range("A2").CopyFromRecordset rs

With the error I mention in the title. And a description of Method 'Copy From Recordset' failed.

However, simply resume after this error pops up takes care of it. And this error pops up intermittently -- sometimes it will go through the entire cycle without erroring out, and sometimes it will happen 2-3 times.

Is there a simple way to simply bypass this? I try to capture this in an error handler, but strangely, when the code stops and the error pops up -- typing ?Err.number in the immediate window produces nothing.

Anyway -- any ideas would be greatly appreciated.

Thanks.

 

[welshgasman]

So is the recordset empty?

 

[CPGDeveloper]

Hi,

No the recordset is never empty in this scenario, but I believe I have found the solution to this issue if anyone else ever comes across it. This seems in-elegant, but I realize that I am able to capture the error in an error handler. In the error handler I tried the following:

If Err.Number = -2147417856 Then
osheet.Range("A2").CopyFromRecordset rs
Resume Next
End If

And while it did capture the error number, it would error out *again* trying to execute the CopyFromRecordset line within the error handling script, so I added a quick subroutine to wait 5 seconds:

Public Sub WaitFor(NumOfSeconds As Long)
Dim SngSec As Long
SngSec = Timer + NumOfSeconds

Do While Timer < SngSec
DoEvents
Loop

End Sub

And added that to my error handler:

If Err.Number = -2147417856 Then
WaitFor (5)
osheet.Range("A2").CopyFromRecordset rs
Resume Next
End If

And so far it seems to be working.

 

[xenou]

That seems to concur with what little else I can find on this error. Is there a lot of code in this routine ... would be curious to try to learn more if you can share some more details.

 

[CPGDeveloper]

Hi xenou,

It is a sizable amount of code...essentially the code is doing the following:

It's looping through a list of queries, and outputting said queries to excel -- the output for each query has its own requirements as far as formatting, titles, grouping etc. and that information is kept in separate 'spec' tables. The code gathers all specs for each query and each column, and loops through each column and applies the formatting accordingly.

The number of queries varies from month to month depending on a set of criteria determined from the data being queried -- it is usually in the 25-30 query range. The 'CopyFromRecordset' fail happens maybe 2-3 times in that loop -- but again, the error handler seems to take care of it.