CODEWITHSUNDEEP
ms-accessvb6adosql-like
Ponytell
Ponytell

Reputation: 73

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

I'm trying to filter records using "Like" with asterisks, it works when using Access 2010 returning many records. I'm stumped why it returns nothing when used with ADO. The code includes multiple tables and columns so to troubleshoot I made a simple query. Here's 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 by "equals" it returns correct record so I'm sure it's able to 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 ways can I filter to give results same as using "Like"? For example, to find all "SVD" products?

Upvotes: 6

Views: 7272

Answers (3)

Paul A Kohn
Paul A Kohn

Reputation: 1

 dim strSQL as string
 dim RC as variant
 dim rs as adodb.recordset
 set rs = new adodb.recordset
 strSQL = "Select * from sometable"

  rs.open strSQL,currentproject.connection, 
  adopenDynamic, adlockOptimistic

  RC = rs.recordcount
  rs.close
  set rs = nothing

is a problem but..

 dim strSQL as string
 dim RC as variant
 dim rs as adodb.recordset
 set rs = new adodb.recordset
 strSQL = "Select * from sometable"

 rs.Open strSQL, CurrentProject.Connection,
 adOpenKeyset, adLockReadOnly

 RC = rs.recordcount
 rs.close
 set rs = nothing

will return the correct record count.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91356

In MS Access, the wildcard is nearly always *, outside of MS Access it is nearly always %, so

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

However, I strongly recommend that you move to parameters to avoid a number of serious problems.

DAO is by far the better choice for ACE / Jet ( rough example Loop table rows in Access, with or without use of Private Const )

Upvotes: 10

Zippit
Zippit

Reputation: 1683

You cannot count on RecordCount. It often returns -1 even if rows were returned. It will only return actual count if you are using a client side cursor.

Instead, use rs.EOF to check for end of recordset. Try something like 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

Upvotes: 0

Related Questions