Help with TRANSFORM in my SQL

[mikemny]

Hello,

I am new to using SQL (and still learning!), and this is my first encounter with the TRANSFORM functionality. I am querying an ODBC database, returning the results in Excel. I was able to get the results I need in Access, but need the query in excel as the inputs will ultimately be dynamic based on other information in the excel file.

I based the below code on the SQL from Access, but am having difficulties. The query returns results (just not pivoted) when I remove the TRANSFORM and PIVOT lines. When I add them, I get a SQL Syntax Error with the line .Refresh BackgroundQuery:=False highlighted.

Sub Macro()
Dim strSQL As String
 
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
      "ODBC;DSN=firm_prod;NA=firmprod1,6100;DB=db_firmprod;UID=X1234;", _
      Destination:=Range("$C$22")).QueryTable
 
      strSQL = "TRANSFORM Sum(report_name.col8) AS (IssSpc)" _
      & " SELECT report_name.row" _
      & " FROM db_firmprod.dbo.report_name report_name" _
      & " WHERE " _
          & "(report_name.choiceID='ABC')" _
      & " GROUP BY  report_name.row" _
      & " ORDER BY  report_name.choiceID, report_excess_return.row" _
      & " PIVOT report_name.choiceID" _
 
      .CommandText = strSQL
 
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .ListObject.DisplayName = "Table_Query_from_firm_prod5"
      .Refresh BackgroundQuery:=False
  End With
End Sub

Any advice would be greatly appreciated!

Thanks!