none
VBA, SQL and WHER clause - error: Automation error RRS feed

  • Question

  • Hi there, 

    The below code does fine when I  don't use the "where" clause.

    Atherwise, I get the automation error: 

    "SELECT * FROM [Clarity$A1:AB2]" - is fine

    "SELECT * FROM [Clarity$A1:AB2] where [A] > 1" - is not fine

    Could you please help ?

    Private Sub CommandButton1_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open strCon
    x = "1611"
    strSQL = "SELECT * FROM [Clarity$A1:AB2] where [A] > 1"

    CritA = "1611"

    'strSQL = "SELECT * FROM [Clarity$A1:AB2] " & "WHERE [A] =  '" & CritA & "'  " & "ORDER BY 1 ASC"

    rs.Open strSQL, cn

    a = rs.GetString

    MsgBox a

    End Sub


    God bless you all :)

    Saturday, May 7, 2016 6:23 AM

Answers

  • The top row must contain column headers (field names), and you must use the field name in the SQL. For example:

    strSQL = "SELECT * FROM [Clarity$A1:AB2] WHERE [" & Worksheets("Clarity").Range("A1").Value & "] > 1"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, May 7, 2016 9:14 AM