Trying to output the data created by TRANSFORM into a local Access table using VBA?

[kutlesh]

I have this query:

TRANSFORM First (MyData2025.K1) AS FirstOfK1
SELECT
    MyData2025.Id1,
    MyData2025.Id2,
    MyData2025.Id3,
    MyData2025.TableId
FROM
    MyData2025
WHERE
    (((MyData2025.TableId) = "0"))
GROUP BY
    MyData2025.Id1,
    MyData2025.Id2,
    MyData2025.Id3,
    MyData2025.TableId PIVOT MyData2025.RowNum;

Its pivoting the data perfectly, except I want to output the data coming from it into a local table.
But there is a catch, I want the code to be able to run on a button click which means, this needs to be programmed into VBA code.
I have it as a VBA function but it doesn't work.

Public Function PivotirajSSOM(ByVal MyData2025 As String) As String
    Dim sql As String
    
    sql = "select * into PivotedDataTable from pdtc where (pdtc.Id1, pdtc.Id2, pdtc.Id3, pdtc.TableId) in (TRANSFORM First (" &  MyData2025 & ".K1) AS FirstOfK1 "
    
    sql = sql & "SELECT "
    
    sql = sql & "" &  MyData2025 & ".Id1, "
    sql = sql & "" &  MyData2025 & ".Id2, "
    sql = sql & "" &  MyData2025 & ".Id3, "
    sql = sql & "" &  MyData2025 & ".TableId "
    
    sql = sql & "FROM " &  MyData2025 & " "
    
    sql = sql & "WHERE " &  MyData2025 & ".TableId = ""0"" "
    
    sql = sql & "Group BY "
    
    sql = sql & "" &  MyData2025 & ".Id1, "
    sql = sql & "" &  MyData2025 & ".Id2, "
    sql = sql & "" &  MyData2025 & ".Id3, "
    sql = sql & "" &  MyData2025 & ".TableId PIVOT " &  MyData2025 & ".RowNum) as pdtc "
    
    PivotirajSSOM = sql
End Function

' Then it would be called like so
DoCmd.RunSQL PivotirajSSOM(MyData2025 )

I am not sure of the syntax for this as I tried few things and it didn't work.

How can I output date from a Crosstab query into a local table using VBA code?