Refresh of just-created pivot table fails with "Method '~' of object '~' failed"

SELECT Top 100 Rtrim(C1.CONTACT) AS [Name], Rtrim(C1.COMPANY) AS Company FROM Contact1 C1

Just as an observation:
TOP now has a preferred syntax of TOP(100) and  results from top is not predictable unless you use an ORDER BY clause.

I have tried recording the creation of a pivot table as a macro using Excel 2016, verifying that the code worked in VBA by running it, then inserted it into my vb6 project.  Now it uses OLEDB as the provider, but same result -- the error above is returned when it gets to the point of refreshing the pivot table.  Here is the new code:

    Dim gExcel As Application
    Dim gWB As Workbook
    Dim wb As Workbook
    Dim PT As PivotTable

    On Error GoTo 0
    
    Set gExcel = CreateObject("Excel.Application")
    gExcel.Workbooks.Add
    Set gWB = gExcel.Workbooks(1)
    gWB.Activate
 
    ' Excel 2016 with OLEDB provider

    gWB.Connections.Add2 Name:="mmConn3", Description:="MasterMine", ConnectionString:=Array("OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password=#####;Data Source=MM-1-VM;Use Procedure for Prepare=1;", "Auto Translate=True;Packet Size=4096;Workstation ID=WIN-83A9QRLN3ML;Use Encryption for Data=False;", "Auto Translate=True;Initial Catalog=GoldMine"), CommandText:="Select Top(100) * from Contact1", lCmdType:=xlCmdSql
    With gWB.Connections("mmConn3").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("Select Top(100) * from Contact1")
        .CommandType = xlCmdSql
        .Connection = Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=######;Persist Security Info=True;User ID=sa;Data Source=MM-1-VM;Use Procedure for Prepare=1;Aut" _
        , _
        "o Translate=True;Packet Size=4096;Workstation ID=WIN-83A9QRLN3ML;Use Encryption for Data=False;Tag with column collation when po" _
        , "ssible=False;Initial Catalog=GoldMine")
        .RefreshOnFileOpen = False
        .SavePassword = True
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With

    gWB.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        gWB.Connections("mmConn3"), Version:=3).CreatePivotTable _
        TableDestination:="Sheet1!R1C1", TableName:="PivotTable2", DefaultVersion:=3
    
    DoEvents
    Err.Clear
    Set PT = gWB.ActiveSheet.PivotTables(1)
    PT.PivotFields(1).Orientation = xlRowField
    PT.PivotFields(2).Orientation = xlDataField
    
    PT.PivotFields(1).Name = "Person"
    PT.PivotFields(2).Name = "Firm"
    
    Err.Clear
    DoEvents
    PT.PivotCache.Refresh ' ERROR OCCURS HERE
    MsgBox "Refresh error if any: " & Err.Description
    Err.Clear
    
    gExcel.Visible = True
    gExcel.Interactive = True
    
ErrHandler:
    If Err.Number <> 0 Then MsgBox "Error " & Err.Number & ": " & Err.Description
    
CloseOut:
     For Each wb In gExcel.Workbooks
        If wb.Name <> "Book1" Then wb.Close
     Next
     Set PT = Nothing
     Set wb = Nothing
     Set gWB = Nothing
     ' Set gExcel = Nothing

          
End Sub

Select allOpen in new window

Possible progress, but I think I need an explanation as I need this to work in vb6.  I tried copying the above code into XLA in a new Excel 2016 workbook.

When I run the code exactly as above in VBA, I get the same error that the compiled VB6 code generates:  Method '~' of object '~' failed

However, if I set gExcel to the current Excel application object, it runs without error and creates the pivot table refreshable.
So for line 8 above, I substituted:  

set gExcel = Application

Select allOpen in new window

Can anyone suggest how to identify which DLL is involved in generating or refreshing this pivot table?  Or explain how it is different between doing so within the current Excel application vs in an Excel object instantiated outside the active one?

Does it help to know that it works correctly with Excel release 16.0.8201.2102 but fails starting with release 16.0.8326.2096?

If you run it as an administrator does it run OK ?

No, it does the same thing.  Thanks for asking.

If i understood correctly you are the creator of this addin...if you have such an issue then you have 2 'solutions":
1st carefully examine your source code to identify the critical point
2nd Process Monitor is the way to go...a lot of time consuming but with a bit of luck you find the troubling point...probably something with registry

The code above is a stand-alone VB6 app that I created to reduce the problem to its simplest form. That's the entirety of the code, plus a form that I use to enter the SQL server parameters and insert them into the connection string at lines 15 and 22.  It errors out at the point indicated (where it says: ' ERROR OCCURS HERE).   Is that the "critical point" you're talking about?

With Process Monitor are you referring to the Sysinternals utility from Microsoft?  I have never been successful figuring out how to use it to track down a thing like this.  Do you have any advice how to narrow down to the DLL or registry setting that might be involved?

In hindsight, it was probably a mistake to mention my app in the original post, as that may give people the impression I'm just debugging code.  This simple demo app clearly demonstrates the problem is a change in Excel, not my other application.  It seems like my best hope is that others will experience the same issue but it may be a small group of users worldwide who build pivot tables programmatically from outside VBA.

The SysInternal  process monitor is the way to find out strange problems related to dlls misconfiguration...just lookout for everything "negative"..like NOT FOUND,DENIED...etc...as for further help i think you should provide a full example (source code+ dlls+data)
Of course there is also the option of Gig...

There was no other relevant feedback.