Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO delete sql not working with LIKE

    Morning. I have a issue where I'm trying to delete records that match a certain pattern.
    The code I'm using is this:
    Code:
    cnnDBServer = New ADODB.Connection
    cnnDBServer.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnnDBServer.Open(strDBPath & "\XYZServer.mdb")
    strSql = "DELETE tblClientFileDetails.* FROM tblClientFileDetails WHERE (((tblClientFileDetails.FileName) Like 'XyzClient*'))"
    cnnDBServer.Execute(strSql)
    cnnDBServer.Close()
    cnnDBServer = Nothing
    The process doesn't delete any of the files. If I replace the strSql with:
    "Delete tblClientFileDetails.* From tblClientFileDetails" It deletes all the records so i know the code works. Also If I paste the original sql into MS Access, it deletes the matching records.
    I'm coding this in Visual studio 2010 and connecting to an external Access database.
    Does anyone see why this doesn't work?
    Thanks,
    Scott

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Some SQL providers use % as the wildcard character rather than *. Give that a try.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    222
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jscher2000 View Post
    Some SQL providers use % as the wildcard character rather than *. Give that a try.
    This is what I have found to be a big difference between DAO and ADO - you have to use % and _ as the wildcard characters in ADO.
    The really frustrating thing is that you can design a query visually in Access using * and it will work through the user interface and DAO, but if you try to execute a stored query including * it will return no results!

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO delete sql not working with LIKE

    Thanks to both. That was the issue exactly.
    Scott

  5. #5
    New Lounger
    Join Date
    Jun 2010
    Location
    Gurnee, IL, USA
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts

    SQL uses % instead of *

    Quote Originally Posted by ready4data View Post
    Morning. I have a issue where I'm trying to delete records that match a certain pattern.
    The code I'm using is this:
    Code:
    cnnDBServer = New ADODB.Connection
    cnnDBServer.Provider = "Microsoft.Jet.OLEDB.4.0"
    cnnDBServer.Open(strDBPath & "\XYZServer.mdb")
    strSql = "DELETE tblClientFileDetails.* FROM tblClientFileDetails WHERE (((tblClientFileDetails.FileName) Like 'XyzClient*'))"
    cnnDBServer.Execute(strSql)
    cnnDBServer.Close()
    cnnDBServer = Nothing
    The process doesn't delete any of the files. If I replace the strSql with:
    "Delete tblClientFileDetails.* From tblClientFileDetails" It deletes all the records so i know the code works. Also If I paste the original sql into MS Access, it deletes the matching records.
    I'm coding this in Visual studio 2010 and connecting to an external Access database.
    Does anyone see why this doesn't work?
    Thanks,
    Scott
    Hi, Scott. This typically happens when you are querying an SQL table, but you seem to be accessing an MDB. In any case, you might want to try using % for a wildcard.
    Regards,
    Kirk

Tags for this Thread

Posting Permissions

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