Searching a table with SEEK

I understand that using SEEK is better than using .FIND when finding a record in a table since .FIND reads the records in the table in sequential order.  But SEEK requires an index.

If I want to use SEEK then how will I connect to the database and open Tables with its corresponding INDEX?  And how will I perform the SEEK command?

I use the following commands to connect to the database and open the table whenever I want to perform a .FIND

   Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Persist Security Info=False;" & " Data Source=" & CheckPath(db_NAME)

  Adodc1.RecordSource = "SELECT * FROM VenueTable ORDER BY VenueName"

with adodc1.recordset
  .FIND search criteria
end with

What is the equivalent in using the SEEK command?


Who is Participating?

Improve company productivity with a Business Account.Sign Up

Valliappan ANConnect With a Mentor Senior Tech ConsultantCommented:
Sorry for the typo error.

rsFind.Open "SELECT * FROM VenueTable Where VenueName = 'search criteria' ORDER BY VenueName",
is wrong. Change it as,

rsFind.Open "SELECT * FROM VenueTable Where VenueName = 'search criteria' ORDER BY VenueName", Conn1, adOpenKeySet, adLockOptimistic
(If you need the results to be editable)

Set rsFind = Conn1.Execute( "SELECT * FROM VenueTable Where VenueName = 'search criteria' ORDER BY VenueName")
(If you need the results as read-only)
Adodc1.RecordSource = "SELECT * FROM VenueTable Where VenueName = 'X' ORDER BY VenueName"
Sub ADOSeekRecord()

   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset

   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;"

   ' Open the recordset
   rst.Open "Order Details", cnn, adOpenKeyset, adLockReadOnly, _

   ' Select the index used to order the data in the recordset
   rst.Index = "PrimaryKey"

   ' Find the order where OrderId = 10255 and ProductId = 16
   rst.Seek Array(10255, 16), adSeekFirstEQ

   ' If a match is found print the quantity of the order
   If Not rst.EOF Then
   Debug.Print rst.Fields("Quantity").Value
   End If

   ' Close the recordset

End Sub

Because Seek is based on an index, it is important to specify an index before searching. In the previous example, this is not strictly necessary because Microsoft Jet will use the primary key if an index is not specified.

In the ADO example, the Visual Basic for Applications Array function is used when specifying a value for more than one column as part of the KeyValues parameter. If only one value is specified, it is not necessary to use the Array function.

As with the Find method, use the NoMatch property with DAO to determine whether a matching record was found. Use the BOF and EOF properties as appropriate with ADO.

The Seek method will work correctly only for Microsoft Jet 4.0 databases. It will fail with a run-time error for all earlier formats, even if you use the Microsoft Jet 4.0 database engine to open the database. This will cause a problem if the application is written to support older database formats. If so, use the Supports method of the Recordset object to determine whether the Seek method is available for the open Recordset. However, if all client applications use the newer format, this check is unnecessary. Use either Microsoft Access 2000 or the CompactDatabase method to convert older databases to the newer format.

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Valliappan ANSenior Tech ConsultantCommented:
I think deighton's one, a better way, of doing it.

The backend will take care of what you want, once, you have created the right index for it, right?

It is faster also, since you are not retrieving all records to recordset.

The question was not "what are the advantages or disadvantages of this or that technique?" but was "how do I use seek with ADO".

I suggested an answer to the question asked.  Clearly, there are many different ways of accomplishing the same thing - DAO/ADO/SQL or some combination which must all be balanced against the back-end and client environment.  Rcbarot provided none of these details, so how can you make a blanket statement about this being better than that?
Valliappan ANSenior Tech ConsultantCommented:

Sorry for that.

I hope it is not necessarily we need to give the exact solution for the question. If we feel that something is better, which the questioner, also, might not have known, we might share it. Nothing wrong about it.

You could see in many questions, that the question is something, and you find, that some alternative solution is arrived at. Many who asked questions, have wondered that there is such a solution.

Still, I am not telling that, Seek is not to be used. Its upto the questioner, to decide. Its just a comment, not a proposed answer, you see. It may be right or wrong, also.

I do understand.

I have found in many instances that using SQL to perform a function is MUCH slower than using a SEEK (if the data source supports a seek operation and is local to the client).
If I understand correctly you need to use a server side cursor when using the seek method with ado. There are a number of articles available on Microsoft's site that provide more detailed info.

try this link:
rcbarotAuthor Commented:
Hello everyone,

I do use SQL searches like the one below but still find it a bit slow when finding a aparticular entry in about 20,000 records.  But would a SEEK be faster?

And, yes, I do appareciate those who can give other more efficient solution methods that I have not asked for.

Adodc1.RecordSource = "SELECT * FROM VenueTable Where VenueName = 'X' ORDER BY VenueName"
Valliappan ANSenior Tech ConsultantCommented:
Did you create a index for the table, with VeneName as the field for index?

Valliappan ANSenior Tech ConsultantCommented:
VeneName should read as VenueName. - typo error.
rcbarotAuthor Commented:
Yes, I did created an index for VenueTable whose field is VenueName.
Valliappan ANSenior Tech ConsultantCommented:
How is the speed after creating the index? Is it better, or had you created the index before itself, and still you get the problem?

What type of Cursor are you using, Client Side or Server Side?

Could you also check what are the network protocols installed in your system, if you are trying the queries from a client instead of from the server. If some of the protocols, are not installed also, I hope, there is a possibility of slower SQL Server running.

Another thing is, to do a FIND in a database, you could also use a recordset for that, like this:

Dim rsFind As RecordSet
Dim Conn1 as Connection

Set Conn1= New ADODB.Connection
Conn1.CursorLocation = adUseClient
Conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Persist Security Info=False;" & " Data Source=" & CheckPath(db_NAME)

Set rsFind = New RecordSet
rsFind.Open "SELECT * FROM VenueTable Where VenueName = 'search criteria' ORDER BY VenueName",

One more improvement, could be, that you declare a public variable for Connection, and you open that connection in your project for once, and then everywhere, you could use that public Connection, instead of opening that connection, everytime.

Hope this helps,

For any clarification, feel free to ask.

rcbarotAuthor Commented:
Thanks a lot!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.