ADO vs DAO

I am switching from DAO to ADO in the next version of my application. The variable strSearch used to work fine with DAO but doesn't want to work in ADO. It uses an SQL LIKE clause.

The strSearch variable value is the result of user input in an input box:

strSearch = InputBox("Type name (or partial) to look for:", "Name")

The recordset is based on an SQL query:
"SELECT Name, Number FROM Patient WHERE Name Like '*" & strSearch & "*'"

The ADO code to open the recordset is as follows:
rstName.Open strSearch, cnnAccident, adOpenDynamic, adLockOptimistic

The DAO code I use to use is
Set rstName = dbsAccident.OpenRecordset(strSearch,dbOpenDyanaset)

Any help is appreciated.
ML

what's happening? are you getting errors or is it just not returning any data?

Try using % as the wild card instead of *

Ian

If that code really is what you are doing I am amazed it ever worked. Are you passing strSearch as an updated SQL string or as the result of the InputBox?

Cheers,

P.

Thanks all for your prompt replies.

Good point Paul, I guess I didn't make it obvious in the open method, I passed the SQL string. I have since changed the variable.

Bigley, it wasn't returning any data.

Ian, I got the same advice as you provided from an online user last night and was able to fix my problem. I was under the (mis)understanding that the % wildcard was to be used only if you are connecting to an SQL Server database.

Thanks again.
Mary Lou

Hi,

Yes i think you misunderstand the usage of wildcards
"*" can be used in Jet Engines, but not in ODBC/OLE DB Providers,so

Be sure that you have right connection string to your database if you use ODBC than even if you use databases other than SQL Server it won't work.So;

use % and _ for wildcards

Good Luck