Transform Query in Access VBA

Question

I have written a VBA code in Access. But, I received syntax error in TRANSFORM statement. I would be grateful if you could help me.

Sub TransformX1()
    Dim dbs As Database
    Dim strSQL As String
    Set dbs = CurrentDb
    strSQL = "TRANSFORM Sum(BAR1.[TON]) AS SumOfTON" _
             & "SELECT BAR1.[MABD],Sum(BAR1.[TON]) AS [Total Of TON]" _
             & "FROM BAR1" _
             & "WHERE (((BAR1.[MABD]) < 1300) And ((BAR1.[MAGH]) < 1300) And ((BAR1.G) = 1))" _
             & "GROUP BY BAR1.[MABD]" _
             & "PIVOT BAR1.[MAGH]"
    DoCmd.RunSQL strSQL
End Sub

RunSQL is for action queries not resultset queries. Additionally, why are you not saving query in database? Stored queries are compiled to best execution plan (i.e., more efficient than VBA string queries called on the fly). — Parfait
– Parfait, Commented May 23, 2018 at 2:43

Sam · Accepted Answer · 2018-05-21 08:18:41Z

1

If you examine your strSQL variable just before the RunSQL command, you'll find that you are missing a few spaces. Replace

"TRANSFORM Sum(BAR1.[TON]) AS SumOfTON" _

with

"TRANSFORM Sum(BAR1.[TON]) AS SumOfTON " _

and so on.

answered May 21, 2018 at 8:18

Sam

5,7411 gold badge21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Shaw Over a year ago

I have this same exact problem and it's not the spaces. There is something different about the TRANSFORM Statement that we have to do in VBA. Does anyone know?

Sam Over a year ago

If you do a Debug.Print strSQL after building it, what do you get?

Collectives™ on Stack Overflow

Transform Query in Access VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related