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

[SidCharming]

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

[SidCharming]

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)

[Micron]

Given the code you posted, it looks like an Excel issue. At any rate, I think the rest of the code is, in fact, necessary. Examples as to why would be that execution at the line in question might be branching to an error handler that can't be processed. Or you're using Automation, in which case the error might be raised on either side. Or you've turned off warnings and an error that causes an object to be lost isn't evident. Or you're also dealing with some other application such as sql server, and that is where the problem lies.

If your message is understood, it means that you might encounter that line 25 times during the execution of this procedure and approximately 5 times out of that 25 the error will be raised.

[SidCharming]

Mircon, thank you for the insight!

I am looking closer at your suggested avenues of possibilities ... SQL, Excel, Access or VBA itself. Here is more above the code

'-- ===============
                            '-- POPULATE Skill
                            '-- ===============
                            
                            MySQL = "SELECT S.Skill  "
                            MySQL = MySQL + "FROM ((tblSkills S INNER JOIN tblPractice P ON S.PracticeID = P.aID) INNER JOIN tblProjectRole PR ON S.ProjectRoleID = PR.aID) INNER JOIN tblServiceTypes ST ON S.ServiceTypeID = ST.aID "
                            MySQL = MySQL + "WHERE ST.ServiceName = " & Chr(39) & vItem & Chr(39) & " AND PR.ProjectRoleAcronym = " & Chr(39) & ary(2, Child) & Chr(39) & " AND P.PracticeCode = " & Chr(39) & ary(3, Child) & Chr(39)
                            MySQL = MySQL + " ORDER BY s.skillNo;"
                              Debug.Print MySQL
                            Set rs = db.OpenRecordset(MySQL)
                              rs.MoveFirst '-- used to get Count records
                              rs.MoveLast  '-- used to get Count records
                            CountSkills = rs.RecordCount    '-- Count records
                            rs.MoveFirst
                            
                            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

[SidCharming]

... Or you're using Automation...

Yes, I have code the created blank lines before this batch of code. This batch of code is reading rows of data from SQL server table then pasting into newly created rows.

If your message is understood, it means that you might encounter that line 25 times during the execution of this procedure and approximately 5 times out of that 25 the error will be raised.

Yes, that is correct.

[Micron]

I'm not saying I can help, because I'm not proficient in sql server syntax. However, I don't see where I (or maybe anyone) has a chance with just snippets of the code that's involved. In the least, it might provide info for improvement suggestions. Here's one. If you have a large recordset, don't move last to get a count as this can slow things down unnecessarily. You don't really need the count here (the latter following suggestion would handle it). Also, don't invoke a move last without checking that there are records, or else you'll raise an error.
Better to consider Do While Not (rs.BOF And rs.EOF) instead of only moving first. In the case of an inner loop where you need to go only to the end of the rs, Do While Not rs.EOF

[SidCharming]

Thank you Micron for your insight and feedback. I would love to share the full code but it's a snippet of over 1000 lines of code that intermingles. (Probably not the best method for coding, but it's what I have). Sorry that's why I shared the amount that I did... Yes there was more.

From your insight it led me to think about something... if the issue is on Excel with the copy & paste of rows I thought about putting a "PAUSE" in the code after running it manually (at slower speed) which resulted in no errors. So after a few runs with an inserted pause, it resolved the issue of the random error happening. My guess is that the code was working faster than what Excel was and it was tripping on its own feet. I also spoke with one of our Ruby coders and he commented they have to do that from time to time as well (rare, but he recalls at least once in his recent history).

Thanks for the records comments... I'll Adjust my code to accommodate the Do While. Much appreciated feedback! =)

Rob

[Micron]

Glad you got it solved. Is this what your pause looks like?

Public Function Pause(intSecs As Integer)
Dim Start As Variant

Start = Timer
Do While Timer < Start + intSecs
    DoEvents
Loop
End Function

If it's in a standard module, it can be called from anywhere.
Good luck!

[orange]

Solution in related post at https://www.tek-tips.com/viewthread.cfm?qid=1788327

[Micron]

SidCharming - you posted in this forum a few days later than the other one without providing a link to the other. Please read https://www.excelguru.ca/content.php?184 to find out why this isn't cool and can result in your future posts from being answered.

[SidCharming]

Glad you got it solved. Is this what your pause looks like?

Public Function Pause(intSecs As Integer)
Dim Start As Variant

Start = Timer
Do While Timer < Start + intSecs
    DoEvents
Loop
End Function

If it's in a standard module, it can be called from anywhere.
Good luck!

No, this is the pause I am using

Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Error_GoTo


    Dim PauseTime As Variant
    Dim Start As Variant
    Dim Elapsed As Variant


    PauseTime = NumberOfSeconds
    Start = Timer
    Elapsed = 0
    Do While Timer < Start + PauseTime
        Elapsed = Elapsed + 1
        If Timer = 0 Then
            ' Crossing midnight
            PauseTime = PauseTime - Elapsed
            Start = 0
            Elapsed = 0
        End If
        DoEvents
    Loop


Exit_GoTo:
    On Error GoTo 0
    Exit Function
Error_GoTo:
    Debug.Print Err.Number, Err.Description, Erl
    GoTo Exit_GoTo
End Function

[SidCharming]

Cross-posting etiquette accepted and will do in future communications. Thank you for the heads up on the how-to.

[Micron]

Gotta say, I like mine better. Actually, I didn't create it, just adopted it.
Thanks for responding to the cross posting concern. I will have to take you off of my 'ignore' list now.

[orange]

I got one similar to Micron (from ghudson on AWF in Feb 2011). I made it a sub since it doesn't return a parameter.

'---------------------------------------------------------------------------------------
' Procedure : Pause
' Author    : From ghudson Access World  Forums
' Date      : 28/02/2011
' Purpose   : To cause Access to Pause for a specific amount of time(in seconds)
'---------------------------------------------------------------------------------------
'
Public Sub Pause(NumberOfSeconds As Variant)
10  On Error GoTo Pause_Error


    Dim PauseTime As Variant, Start As Variant

20  PauseTime = NumberOfSeconds
30  Start = Timer
40  Do While Timer < Start + PauseTime
50      DoEvents
60  Loop
70  On Error GoTo 0
80  Exit Sub

Pause_Error:

90  MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Pause, line " & Erl & "."

End Sub