Using LIKE operator in ADO recordset 
Author Message
 Using LIKE operator in ADO recordset

I'm feeling very dumb tonight.  I'm trying to create an
ADO recordset based on a SQL source statement -- using the
following code:

   Dim rst as ADODB.Recordset
   Dim MySQL as String

   Set rst = New ADODB.Recordset
   rst.ActiveConnection = CurrentProject.Connection
   rstTest.CursorType = adOpenKeyset
   rstTest.LockType = adLockOptimistic

   MySQL = "SELECT * FROM tblSample WHERE [FirstName]
Like '*J*';"

   rst.open MySQL

This returns an empty recordset, even though there are
records that should be returned by it.

If I open the recordset and declare a filter for it, such
as:
   MYSQL = "SELECT * FROM tblSample;"
   rst.Open MySQL
   rst.Filter = "[FirstName] Like '*J*'"
It works fine, but since I want to loop through a set of
criteria defined dynamically, this method is
excruciatingly slow.

Why won't the first simple declaration work?  I've been
scratching my head all day over this, knowing that I'm
missing something very basic.

Thanks,
Doug DeNatale



Sun, 03 Oct 2004 10:16:16 GMT  
 Using LIKE operator in ADO recordset
On Tue, 16 Apr 2002 19:16:16 -0700, "Doug DeNatale"

Quote:

>   MySQL = "SELECT * FROM tblSample WHERE [FirstName]
>Like '*J*';"

>   rst.open MySQL

>This returns an empty recordset, even though there are
>records that should be returned by it.

The wildcard character in ADO is % rather than *... try changing the
asterisks to percent signs.

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public



Mon, 04 Oct 2004 12:15:50 GMT  
 Using LIKE operator in ADO recordset
Many thanks - I knew I was missing the obvious.

Quote:
>-----Original Message-----
>On Tue, 16 Apr 2002 19:16:16 -0700, "Doug DeNatale"

>>   MySQL = "SELECT * FROM tblSample WHERE [FirstName]
>>Like '*J*';"

>>   rst.open MySQL

>>This returns an empty recordset, even though there are
>>records that should be returned by it.

>The wildcard character in ADO is % rather than *... try
changing the
>asterisks to percent signs.

>                  John W. Vinson[MVP]    
>    Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
>.



Mon, 04 Oct 2004 09:25:16 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Finding date field using LIKE operator using ADO

2. ADO Find using Like operator

3. Visual Liking in .Net using TREE VIEW control

4. Problem using VB to retrieve ASP/XML using ADO recordset

5. Open ADO recordset on another ADO recordset - possible?

6. LIKE operator in filter property of recordset (for numeric fields)

7. NOT LIKE OPERATOR ON ADODB RECORDSET

8. URGENT MICROSOFT SUPPORT ISSUE - using ADO recordsets bound to Access .mbd Formsat runtime

9. Using an ADO recordset for a report

10. Batch update for multiple tables using single ADO Recordset (VB )

11. Oracle recordsets using ADO

12. Detecting how many recordsets are using an ADO connection

 

 
Powered by phpBB® Forum Software