Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Posts
    12

    Unanswered: SQL command LIKE and ADO

    Hi,

    I ran into this odd problem when trying to make a query in Excel VBA with ADO. When using SQL clause:

    "SELECT * FROM Table" : everything works fine and records are found.

    But when using:
    "SELECT * FROM Table WHERE Name LIKE 'test*'" no records are found.

    The odd thing is when I copy the clause straight to Access and make a query, Access finds these 'like' records.

    The code I'm using is straight from VBA help so it should work, right? 8-)

    JN

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    When using ADO, use % as the wildcard character instead of *.

    The reason it still works in the QBE grid is because Access QBE is still
    DAO.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    RNG: I can use the * wildcard when setting up an ADO recordset within Access, is the % required when using ADO with different office libraries?

    Thx.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    HomerBoo

    Good catch!
    Maybe I answered to quickly (without thinking, of course)....
    My data is all stored in SQL Server, and I just use "%" out of
    habit. My answer may be wrong.

    Mark
    Inspiration Through Fermentation

  5. #5
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    RNG: Wasn't meant to catch, I haven't created many recordsets out of non-Access/Oracle/SQL Server data, and wanted to know for future use.

    jnyman: Can you provide a little more detail like what you are connecting to through ADO? Not sure where 'Table' is. Keep in mind that names like 'Table', 'Name', 'Reference', etc can be considered reserved names in some cases, and you may be unwittingly confusing your computer.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  6. #6
    Join Date
    Dec 2002
    Posts
    12
    I'm using Excel VBA to connect to Access database with ADO 2.5 library (reference).

    RedNeckGeek: Tried already using % instead of *, but no avail. Forgot to mention that.

    HomerBoo: The 'Table' and 'Name' are not the real names of the objects, they might as well be called 'MyTable' and 'CustomerName', so that kind of confusion is ruled out.

    But the weird thing is that without the "LIKE 'something'" the query works just fine.

    Anyway, here's the whole code:

    '-------------------------------------------------

    Sub ADOConnection()

    Dim cnYhteys As ADODB.Connection
    Dim rstTietojoukko As ADODB.Recordset
    Dim adoKomento As ADODB.Command

    Dim strLause As String

    Set cnYhteys = New ADODB.Connection
    With cnYhteys
    .Provider = "Microsoft.Jet.OLEDB.4.0;"
    .Open "Data Source=C:\Access\MyDatabase.mdb;"
    End With

    strLause = "SELECT * FROM MyTable WHERE CustomerName LIKE 'test*'"

    ' strLause="SELECT * FROM MyTable" ' this query would work

    Set adoKomento = New ADODB.Command
    Set adoKomento.ActiveConnection = cnYhteys

    With adoKomento
    .CommandText = strLause
    .CommandType = adCmdText
    .Execute
    End With

    Set rstTietojoukko = New ADODB.Recordset
    Set rstTietojoukko.ActiveConnection = cnYhteys
    rstTietojoukko.Open strLause, cnYhteys

    [b20].CopyFromRecordset rstTietojoukko

    rstTietojoukko.Close
    cnYhteys.Close

    Set adoKomento = Nothing
    Set rstTietojoukko = Nothing
    Set cnYhteys = Nothing

    End Sub

    '-------------------------------------------------

    jnyman

  7. #7
    Join Date
    Sep 2003
    Location
    Lancashire, UK
    Posts
    22
    Have you tried to do this with DAO instead of ADO, just a thought but may be worth a try

  8. #8
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    This is what I used. Seems to work. Seems RNG was on to something:

    Sub ADOConnectionNew()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\YourDBPathAndName" ';User Id=admin;Password="
    rst.Open "SELECT * FROM tblText WHERE FirstCol LIKE 'ap%';", cnn, adOpenStatic, adLockReadOnly
    [a1].CopyFromRecordset rst

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    End Sub
    All code ADO/ADOX unless otherwise specified.
    Mike.

  9. #9
    Join Date
    Dec 2002
    Posts
    12

    Thanks!

    It works with your code, thank you HomerBoo!

    jnyman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •