Error in FROM clause

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Hello Everyone,

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

Code:
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
revised code

Hello Everyone,

I now have the following code:

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
 

Forum statistics

Threads
1,141,680
Messages
5,707,788
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top