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));"

 

[krice1974]

Hi James, I'm working on a similar approach, stripping elements away to see what it doesn't like. Denis, thanks but it didn't work, the quotes there seem to throw it so it wants table names declared as variables etc. Learning to program: every code snippet is an experience!!

Happy Labor Day.

 

[krice1974]

OK, I just tried a SQL with a similar subquery. It generates the same error. Any ideas? Could it be the functions (I guess I'd get an error specific to that) Does anyone know if the subquery is "do-able" when passed from excel vba this way? Here's the latest try:

'Set the SQL string.
sSQL = "SELECT tblStocksPricingVol.Symbol, tblStocksPricingVol.PricingVolDate FROM " & _
    "tblStocksPricingVol WHERE ((tblStocksPricingVol.PricingVolDate)=(SELECT Min(T2.PricingVolDate)" & _
    "FROM tblStocksPricingVol AS T2 WHERE T2.Symbol = tblStocksPricingVol.Symbol));"

 

[RoryA]

It can certainly be done. What does the line that opens the recordset look like?

 

[SydneyGeek]

I'm guessing that the issue is with the connection rather than the query; also, FormatPercent makes Excel VBA choke. This worked for me (you need to set a reference to the Microsoft ActiveX Data Objects 2.8 Library) --

Sub QueryData()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim MyConn
    Dim i As Long
    Dim ShDest As Worksheet
    Dim sSQL As String

    Set ShDest = Sheets("Data")

    sSQL = "SELECT tblStocksPricingVol.Symbol, tblStocksPricingVol.PricingVolDate, tblStocksPricingVol.Volume, " & _
        "tblStocksPricingVol.HighPrice, tblStocksPricingVol.ClosePrice, [HighPrice]-[ClosePrice] AS " & _
        "DiffBetwClAndHigh, Format([DiffBetwClAndHigh]/[HighPrice],""0.00%"") AS PctClFromHigh " & _
        "FROM tblStocksPricingVol " & _
        "WHERE (((tblStocksPricingVol.PricingVolDate)=" & _
        "(SELECT Max(T2.PricingVolDate)" & _
        "FROM tblStocksPricingVol AS T2 " & _
        "WHERE T2.Symbol = tblStocksPricingVol.Symbol)) " & _
        "AND ((tblStocksPricingVol.Volume)>=850000));"
    
    Set cnn = New ADODB.Connection
    MyConn = "C:\Users\Denis\Documents\Stocks.mdb"
    
    With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open MyConn
    End With
    
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=sSQL, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenForwardOnly, _
            LockType:=adLockOptimistic, _
            Options:=adCmdText

    'clear existing data on the sheet
    ShDest.Activate
    Range("A1").CurrentRegion.Offset(1, 0).ClearContents
    
    'create field headers
    i = 0
    With Range("A1")
    For Each fld In rst.Fields
      .Offset(0, i).Value = fld.Name
      i = i + 1
    Next fld
    End With
    
    'transfer data to Excel
    Range("A2").CopyFromRecordset rst
    
    ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
End Sub

Denis

 

[krice1974]

Here's the whole thing, I should have posted it sooner:

Sub ClosedNearHighAddToPOI()
'Run the query that writes symbs that closed at or near their highs, with a vol over 850,000
'to the tblPointsOfInterest.

Dim sSQL As String

'Set the worksheet reference.
Set AQW = Worksheets("AccessQueriesWorkspace")

'Set command object.
Set objCommand = New ADODB.Command
objCommand.ActiveConnection = sConnect

'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));"
    
'Create the recordset and run the query.
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable

'Make sure we got records back.
    If Not rsData.EOF Then
        'Add the data to the worksheet.
        AQW.Range("A2").CopyFromRecordset rsData
        'Close the recordset object.
        rsData.Close
    Else
        'Close the recordset object.
        rsData.Close
    End If

End Sub

 

[RoryA]

Change adCmdTable to adCmdText on the line that opens the recordset.

 

[krice1974]

Denis, our last emails passed in the mail. I'm trying that now, thanks for the time spent.

 

[james_lankford]

Change adCmdTable to adCmdText on the line that opens the recordset.

LOL
yeah, it would have been good to see the whole thing

I'm sure that will fix it for you, glad you'll get it working

 

[RoryA]

 

[krice1974]

Rory and Denis, changing adCmdTable to adCmdText did it. Scha- weet! Any feedback as to specifics on this? adCmdTable works in other procedures, for this one I just copy/ pasted another to reuse most of the code.

Thanks all for the help and patience. I love getting past a bug. (however noobish

Kevin