SQL string error in excel vba

[krice1974]

Hi. I'm trying to execute a SQL command to pull an access query into excel. The select query works perfectly in access, and I've pasted the SQL into the procedure below. When running it I'm getting "syntax error in FROM clause." Any ideas? Thanks in advance... Kevin

'Set the SQL string.
sSQL = "SELECT tblStocksPricingVol.Symbol, tblStocksPricingVol.PricingVolDate, tblStocksPricingVol.Volume, " & _
    "tblStocksPricingVol.HighPrice, tblStocksPricingVol.ClosePrice, [HighPrice]-[ClosePrice] AS " & _
    "DiffBetwClAndHigh, FormatPercent([DiffBetwClAndHigh]/[HighPrice]) AS PctClFromHigh FROM " & _
    "tblStocksPricingVol WHERE (((tblStocksPricingVol.PricingVolDate)=(SELECT Max(T2.PricingVolDate)" & _
    "FROM tblStocksPricingVol AS T2 WHERE T2.Symbol = tblStocksPricingVol.Symbol)) AND " & _
    "((tblStocksPricingVol.Volume)>=850000));"

 

[RoryA]

adCmdTable is designed for use when you are simply passing a table name to the procedure (although you do need to use it when using an Access parameter query for some reason); adCmdText is supposed to be used when using a SQL string as you are here.

 

[krice1974]

Many thanks.