Is it possible to use an excel query connection as an adodb.connection source

See more:

VB

MS-Excel

Good day all,

I have imported data into my excel datamodel and have been able to import that back into a sheet using :

Application.CutCopyMode = False
    Application.DisplayAlerts = False
    
    Dim tbl As ListObject
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets.Add
    
    connectionName = fieldName & "sConnection"
    
    With ws.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook.Connections("Query - 
        TRENDS_GroupData"), Destination:=Range("$A$1")).TableObject
        .WorkbookConnection.OLEDBConnection.CommandText = Array( _
            "Evaluate" & Chr(13) & "" & Chr(10) & "Calculatetable" & Chr(13) & "" & 
             Chr(10) & "(DISTINCT(UNION(" & Chr(13) & "" & Chr(10) & 
             "Distinct(Trends_MyCoData[" & fieldName & "])," & Chr(13) & "" & Chr(10) 
             & "Distinct(Trends_GroupData[" & fieldName & "])" & Chr(13) & "" & 
             Chr(10) & ")))")
        .WorkbookConnection.OLEDBConnection.CommandType = xlCmdDAX
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = fieldName
    End With
    
    Set tbl = ActiveSheet.ListObjects(1)
    tbl.Refresh

In another workbook I use an ADODB.Connection to query a table on a worksheet with a table using :

Public Function CreateSQLConnection() As Object
   
    '---Connecting to the Data Source---
    Set CreateSQLConnection = CreateObject("ADODB.Connection")
    With CreateSQLConnection
        .Provider = "Microsoft.Mashup.OleDb.1"
        .ConnectionString = "Data Source=ActiveWorkbook.Connections(""Query - 
         TRENDS_GroupData"");" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
        .Open
    End With

End Function

These to options have been working great however I have now come accross a much larger dataset and was wondering if it is possible to use the Connection as a source for the ADODB.Connection as I have so far met with no success.

The closest I have come to getting it to work pops up the following message :
"Cannot update. Database or object is read-only."

All in all my end goal is to be able to query the data model using SQL, if there is another way to do it instead of the above I would appreciate the guidance.

Any thoughts would be highly appreciated.

What I have tried:

Changing the mode to Read or adRead
Changing the provider to JET or Mashup
A few permutations of different settings.