No such interface supported - Error '-2147417856 (80010100)'

[03Explorer]

I have code that runs great for some time and then it stops with this error on the Same line of code.

 Run-time error '-2147417856 (80010100)':
No such interface supported

I choose "DEBUG" and then immediately press F5 to <continue>... everything executes fine for a random period of time and then the error pops up again. It pops up about 5 times which I've tracked the number of times that line is hit all together is around 25 times... The error happens at the same point all the time. Any ideas? This is a show stopper for me.

Thanks!
Rob

P.S. My code is lengthy but the CopyFrom Recordset is the line of issue (below)

For i = (SR) To (SR + (CountSkills - 1)) '-- inserts 2 minus the ones already there (top one7 & bottom one8)
     '-- Copy from SHEET(1)row8 recordCount times (for inserted Rows)
     MyXL.Sheets(1).Rows(8).copy
     '--  Insert beginning at row SR
     MyXL.ActiveSheet.Rows(i).Insert shift:=xlDown
Next i
MyXL.ActiveSheet.range("C" & SR).CopyFromRecordset rs '----!!! Crashes here?
'rs.Close  '-- attempt to avoid odd error
Set rs = Nothing

 

[03Explorer]

I believe it's a memory issue because ONE of the many times of trying to figure out the error I got an out of Memory error... which I also went into debug mode and immediately pressed F5 (Continue) and it chugged away until the next interface error (which happens at the same point indicated above)

 

[03Explorer]

A solution is found. After much time spent it was identified that Excel must be taking more time than what the code was allowing for the copy and paste of blank rows that the data population was too soon which in essence was causing the app to stumble on its own feet. My solution was to put in a pause for 1 second before the CopyFromRecordset. So it wasn't a memory issue but a speed issue with using Excel from Access.

 

[lameid]

I am glad you found it.

I would expect all that to happen synchronously. You describe a race condition in behavior from an automated application but you are automating it with vba. How can this be? FWIW I have cases where I move files but the original is picked up by the next DIR call. All native Access commands so I would expect synchronous operation.

These things get in my crawl too. Since all mine have been OS level things, I have been blaming mine on security products and writing stupid code to validate the change happened.

I wonder if there is some sort of optimization MS decided to use to process single core apps multi-threaded... that could do it.