Seek or find in ADO & Jet

I need to check the existence of records by ID in a loop. Under DAO seek worked much faster than repeatedly running an SQL query under ADO and checking if the resulting recordset is empty.
Is there any way of using seek or find under ADO? or is there some other fast way under ADO?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

There is a way of using seek or find under ADO.  There are two functions of using these in ADO which are Filter and Find.  The statements of using them are :

Dim rsSearch As New ADODB.Recordset

'Then connect and put data into recordset

rsSearch.Find = "ID = " & search_key
If rsSearch.Fields("ID") <> search_key then
'Not Exist
End If


'For the use of Filter

rsSearch.Filter = "ID = " & search_key
If rsSearch.RecordCount <= 0 Then
'Not Exist
End If

Before using the Find function provided by the ADO method, you can use the function SORT in order to fasten the search speed.  The usage is :
rsSearch.Sort = "ID"

Besides, by using Find and Filter function, you are able to use "Like" keyword in it instead of "=", i.e. rsSearch.Find = "field_name like '*" & search_text & "*'"

Hope this may help you.
gregbinnsAuthor Commented:
When I try either of the above methods I get error 3251 "The operation requested by the application is not supported by the provider."
This may be caused by the incorrect database connection string.  To get a correct database connection string with a correct provider, there is a simple way.  First, add a ADO data control component,which called ADODC, into your form.  Then choose the property 'ConnectionString' and click on the button.  A dialogue box will then appear.  Choose "Use Connection String" and click the "Build" button and setup all your needed criteria.  And you will get a string in the property eventually.  Then copy the string and paste into the coding part where the database connection procedure located.  Then the problem will then be solved.

Or you can check out the version of your ADO and make sure it is 2.0.  
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Éric MoreauSenior .Net ConsultantCommented:
You need ADO 2.1 in order to use the Seek method.

Get the latest at
If the above is not solving your problem, there may be a problem with how the recordset is being opened.

use something like sqlstatement, ADOConnectionObject, adOpenKeySet)

 rs.Filter statement should then work.

if you use rs = ADOConnection.Execute(sqlstatement)
the recordset has a forward-only cursor and does not support the Filter method.
gregbinnsAuthor Commented:
I have installed ADO 2.1 from mdac_typ from microsoft.
The connection string is
Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;User ID=Admin;Data Source=FILENAME;Mode=Share Deny None;Extended Properties=";COUNTRY=0;CP=1252;LANGID=0x0409";Locale Identifier=1033;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Global Partial Bulk Ops=2
Seek Find and Filter still return the same error message.
quoting from MSDN - REMARKS under RecordSet.Seek

"Use the Seek method in conjunction with the Index property if the underlying provider supports indexes on the Recordset object. Use the Supports(adSeek) method to determine whether the underlying provider supports Seek, and the Supports(adIndex) method to determine whether the provider supports indexes. (For example, the OLE DB Provider for Microsoft Jet supports Seek and Index.)

If Seek does not find the desired row, no error occurs, and the row is positioned at EOF. Set the Index property to the desired index before executing this method.

This method can only be used when the Recordset object's CursorLocation property has a value other than adUseClient.

This method can only be used when the Recordset object has been opened with a CommandTypeEnum value of adCmdTableDirect."

So if you are using an SQL statement, it seems like RecordSet.Seek just will not work !

As regards RecordSet.Find, it only works against criteria for single columns; if you do position the cursor within the recordet first (i.e. Call RecordSet.MoveFirst) then the Find method will fail.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gregbinnsAuthor Commented:
It still doesn't work.  Is it possible that although I have installed MSDAC 2.1 that in the dev enviorenment I am still using an earlier version of ADO?
I am getting desperate!
gregbinnsAuthor Commented:
I sorted it out in the end. Thank You
As a matter of interest, what was the solution in the end ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.