The "LIKE" operator works in MS Access, but not ADO

Question

I am trying to filter records using "Like" with asterisks, it works when Access 2010 returns a lot of records. I do not understand why it does not return anything when used with ADO. The code contains several tables and columns, so I made a simple query for troubleshooting. Here is the code:

strsql = "SELECT tproducts.Prod_Name FROM tproducts " _ & " WHERE tproducts.Prod_Name Like " & Chr(34) & "SO*" & Chr(34) Set cn = New ADODB.Connection cn = connString cn.Open Set rs = New ADODB.Recordset rs.Open strsql, cn, adOpenStatic, adLockOptimistic ' test here iRecCount = rs.RecordCount rs.MoveFirst 

Recordcount returns -1.

When "Like" is replaced with "equals", it returns the correct record, so I'm sure that it can connect to the database, for example:

 strsql = "SELECT tproducts.Prod_Name FROM tproducts " _ & " WHERE tproducts.Prod_Name = " & Chr(34) & "SONY Vaio SVD13213CXB" & Chr(34) 

Is there a special way to use the Like operator in ADO?

What other methods can be filtered to give results in the same way as with "Like"? For example, to find all the products "SVD"?

Answer 1

In MS Access, the wildcard is almost always *, outside of MS Access it is almost always%, therefore

 str = "SELECT tproducts.Prod_Name FROM tproducts) " _ & " WHERE tproducts.Prod_Name Like ""SO%""" 

However, I strongly recommend that you go to options to avoid some serious problems.

DAO is by far the best choice for ACE / Jet (example Loop table row in Access, with or without Private Const )

Answer 2

You cannot count on RecordCount. It often returns -1, even if the rows were returned. It will return the actual invoice if you use the cursor on the client side.

Instead, use rs.EOF to check the end of the recordset. Try the following:

 Set cn = New ADODB.Connection cn = connString cn.Open Set rs = New ADODB.Recordset rs.Open strsql, cn, adOpenStatic, adLockOptimistic ' very innefficient way to find the record count, but gives you the idea. If you just care about record count use "COUNT(*)" in your query do while not rs.eof iRecCount = iRecCount + 1 rs.MoveNext loop