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

Question

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?

score 10 · Accepted Answer · edited May 23 '17 at 11:45

10

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 )

edited May 23 '17 at 11:45

Community

1
1

answered Sep 03 '14 at 00:45

Fionnuala

90,370
7
114
152

Yes, I used asterisk wildcard. It used to be connected to a Mysql database and uses %, however, % returns nothing in MS Access. I tested the statements directly onto MS Access. – Ponytell Sep 03 '14 at 00:51
You are using ADO, so that is %, not * If you are working with ACE / Jet, it is much more efficient to use DAO. – Fionnuala Sep 03 '14 at 00:53
The query is just being passed through ADO to the Access engine (JET engine). ADO doesn't parse the query itself. – Zippit Sep 03 '14 at 00:56
@Zippit The point is the same, with ADO, the query must use %, with DAO * is an option. DAO is much faster when using ACE / Jet. – Fionnuala Sep 03 '14 at 00:57
I switched to % wildcard but same result, no records were returned. – Ponytell Sep 03 '14 at 01:02
Why are you using ADO with ACE / Jet? You can get a recordset that is easy to check the count if you use DAO. – Fionnuala Sep 03 '14 at 01:06
Yes I'm considering using DAO but might take a few months to migrate, so not an option at this time. – Ponytell Sep 03 '14 at 01:07
I stand corrected. I wrote a test app in VBScript (basically the same engine as VB6 with some minor changes) and it does require the '%' character. '*' does not work. I apologize. – Zippit Sep 03 '14 at 01:14
Hey, @Zippit, I think we both have valid points : http://www.adopenstatic.com/faq/recordcounterror.asp – Fionnuala Sep 03 '14 at 01:15
Use ALike in MS Access query design window and see what you get back. ALike will allow you to test % for results. – Fionnuala Sep 03 '14 at 01:17
Just for giggles, can you try changing your query as follows (note the removal of the CHR(34) calls): str = "SELECT tproducts.Prod_Name FROM tproducts WHERE tproducts.Prod_Name Like 'SO%' " – Zippit Sep 03 '14 at 01:20
@Zippit you may need to watch that for embedded single quotes, yesno? – Fionnuala Sep 03 '14 at 01:24
OH, absolutely. This is just a proof of concept to get it working. You still need to harden it against SQL-Injection. Like you said, use parameters. Of course, that would still be an issue with the CHR(34) as well. – Zippit Sep 03 '14 at 01:25
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/60486/discussion-between-zippit-and-fionnuala). – Zippit Sep 03 '14 at 01:32
@Zippit, as you suggested this one worked! strsql = "SELECT tproducts.Prod_Name FROM tproducts " _ & " WHERE tproducts.Prod_Name Like '%SVD%'" – Ponytell Sep 03 '14 at 02:22
Just a minute, % is my suggestion. Are you saying that you do not have items `LIKE 'SO%'` ? – Fionnuala Sep 03 '14 at 02:28
@Zippit, as you suggested this one worked! strsql = "SELECT tproducts.Prod_Name FROM tproducts " _ & " WHERE tproducts.Prod_Name Like '%SVD%'" It does not return records on Access but works sweet with ADO. So, in conclusion: - don't use chr(34), use single quote instead - use percent % instead of asterisk. Thanks a million! – Ponytell Sep 03 '14 at 02:30
@ Fionnuala: yes there are over a hundred "Sony" products. Thanks also a million to you for your time and effort to help me out. I'll remember to use % in ADO and * in Access. – Ponytell Sep 03 '14 at 02:36
You should double the quote characters to [include a quote literal](http://stackoverflow.com/q/8574682/588306) or use single quotes where they're supported. – Deanna Sep 03 '14 at 08:37
Searching further I found this info from the book 'SQL for Microsoft Access' by Cecelia L. Allison and Neal A. Berkowitz, page 237: "If your Access database is set to SQL-89 use an asterisk (*) instead of a percent sign (%) in the above SQL script." – Ponytell Sep 04 '14 at 23:52
Also just found a discussion about this issue: "[Microsoft Jet wildcards: asterisk or percentage sign?](https://stackoverflow.com/questions/719115/microsoft-jet-wildcards-asterisk-or-percentage-sign)" – Ponytell Sep 05 '14 at 00:02

Zippit · Answer 2 · 2014-09-03T00:53:26.117

0

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

edited Sep 03 '14 at 00:53

answered Sep 03 '14 at 00:47

Zippit

1,673
1
11
11

But the problem is that "Like" does not always return any record. – Ponytell Sep 03 '14 at 00:56
How do you know it is not returning any records? As I mentioned above, if you only checking rs.recordCount then you are wrong. You need to check for rs.eof – Zippit Sep 03 '14 at 01:04
rs.movenext returns error: Runtime error '3021'. Either BOF or EOF is true, or the current record has been deleted. – Ponytell Sep 03 '14 at 01:09
Heh...then you're definitely not getting any records back. – Zippit Sep 03 '14 at 01:15
So, it seems "Like" does not work on ADO. Is there another way of filtering with wildcards? – Ponytell Sep 03 '14 at 01:20
Yes it does. See the comments in Fionnuala's answer. I wrote a test app just a few minutes ago using Access and ADO and it works fine. – Zippit Sep 03 '14 at 01:26
@Fionnuala & Zippit, sorry I wish I could but I can't vote at this time. – Ponytell Sep 03 '14 at 02:48
@SiegfredNeval Do not worry, things sort themselves out. – Fionnuala Sep 03 '14 at 03:25

score 0 · Answer 3 · answered Nov 20 '22 at 00:22

 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.

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

3 Answers3

Linked