#1
  1. 'fie' on me, allege-dly
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2003
    Location
    in da kitchen ...
    Posts
    12,894
    Rep Power
    6444

    Using VBA to pull Data from an Access database into Excel


    I have a button in an excel spreadsheet, which should build an sql statement on the fly based on the contents of cell A1, and based on the SQL built return the ResultSet from the query

    Code:
    Watch :   : SelectStatement : "SELECT Customers.* FROM Customers WHERE Also_known_as LIKE 'Tak*'" : String : Sheet1.SearchAddress_Click
    This is the statement it's building and when I enter the text below into the SQL query window, it returns the expected results, any ideas as to what I'm missing? Or what do I need to look for ...

    Code:
    SELECT Customers.* FROM Customers WHERE Also_known_as LIKE 'Tak*'
    vba Code:
    Private Sub SearchAddress_Click()
     
    Dim Connection As New ADODB.Connection
    Dim ResultSet As New ADODB.Recordset
    Dim SelectStatement As String
     
    ' Drive code for Access
    Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp.mdb"
    ' open database
    Connection.Open
     
    Set ResultSet.ActiveConnection = Connection
     
    SelectStatement = "SELECT Customers.* " & _
         "FROM Customers " & _
         "WHERE Also_known_as LIKE '" & _
         ActiveSheet.Cells(1, 1).Value & _
         "*'"
    MsgBox SelectStatement
     
    ResultSet.Open SelectStatement
    Do Until ResultSet.EOF
        MsgBox (ResultSet.Fields(0).Value & "," & ResultSet.Fields(1).Value & "," & _
            ResultSet.Fields(2).Value & "," & ResultSet.Fields(3).Value & "," & _
            ResultSet.Fields(4).Value & "," & ResultSet.Fields(5).Value & "," & _
            ResultSet.Fields(6).Value & "," & ResultSet.Fields(7).Value & "," & _
            ResultSet.Fields(8).Value & "," & ResultSet.Fields(9).Value & "," & _
            ResultSet.Fields(10).Value & "," & ResultSet.Fields(11).Value)
    Loop
     
    End Sub
    --Ax
    without exception, there is no rule ...
    Handmade Irish Jewellery
    Targeted Advertising Cookie Optout (TACO) extension for Firefox
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones


    09 F9 11 02
    9D 74 E3 5B
    D8 41 56 C5
    63 56 88 C0
    Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.
    -- Jamie Zawinski
    Detavil - the devil is in the detail, allegedly, and I use the term advisedly, allegedly ... oh, no, wait I did ...
    BIT COINS ANYONE
  2. #2
  3. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    DAO vs ADO I believe you might have better luck with the % wildcard than the * wildcard ... try it?
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  4. #3
  5. 'fie' on me, allege-dly
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2003
    Location
    in da kitchen ...
    Posts
    12,894
    Rep Power
    6444
    Was just coming back to say use % rather than *, * works in access SQL, but not in VBA

    Cheers Lint
    --Ax
    without exception, there is no rule ...
    Handmade Irish Jewellery
    Targeted Advertising Cookie Optout (TACO) extension for Firefox
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones


    09 F9 11 02
    9D 74 E3 5B
    D8 41 56 C5
    63 56 88 C0
    Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.
    -- Jamie Zawinski
    Detavil - the devil is in the detail, allegedly, and I use the term advisedly, allegedly ... oh, no, wait I did ...
    BIT COINS ANYONE

IMN logo majestic logo threadwatch logo seochat tools logo