Error -2147417856 (80010100)

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
157
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
157
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.
 
Solution

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,828
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Oct 8, 2008
Messages
157
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top