Error in FROM clause

[Kurt]

Hello Everyone,

I got some great help yesterday! I am now down to the last piece I hope!

Sub command_dyer3()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Dim countryname$
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\From Dyer.mdb;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
    strSQL = "TRANSFORM Sum([track apps].Apps) AS SumOfApps "
    strSQL = strSQL & "SELECT dbo_SMT_Branches.BranchTranType "
    strSQL = strSQL & "FROM [track apps] LEFT JOIN dbo_SMT_Branches ON [track apps].Branch = dbo_SMT_Branches.BranchNbr "
    strSQL = strSQL & "GROUP BY dbo_SMT_Branches.BranchTranType "
    strSQL = strSQL & "PIVOT [track apps].MONTH;"
    comm.CommandText = strSQL
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

I get the error message Syntax error in FROM clause.

The code stops on the rec.Open comm line.

TIA

Have a great day!

Kurt

 

[Kurt]

revised code

Hello Everyone,

I now have the following code:

Sub command_dyer3()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\From Dyer.mdb;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
    strSQL = strSQL & "SELECT Branch"
    strSQL = strSQL & "FROM track apps"
    comm.CommandText = strSQL
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

It stops on the FROM statement with the error message Invalid SQL statement.

What am I doing wrong here? This select from should be easy!

TIA

Kurt