ADO delete sql not working with LIKE

Topic

New Reply

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

Reply | Quote

Replies

Some SQL providers use % as the wildcard character rather than *. Give that a try.

Reply | Quote

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!

Reply | Quote

Thanks to both. That was the issue exactly.
Scott

Reply | Quote

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

Reply | Quote