[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 667
  • Last Modified:

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?
0
gregbinns
Asked:
gregbinns
  • 4
  • 3
  • 2
  • +1
1 Solution
 
rinkelCommented:
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.
0
 
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."
0
 
rinkelCommented:
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.  
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Get the latest at www.microsoft.com/data
0
 
grumpy147Commented:
If the above is not solving your problem, there may be a problem with how the recordset is being opened.

use something like

 rs.open( 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.
0
 
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.
0
 
grumpy147Commented:
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.
0
 
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!
0
 
gregbinnsAuthor Commented:
I sorted it out in the end. Thank You
0
 
grumpy147Commented:
As a matter of interest, what was the solution in the end ?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now