Searching a table with SEEK

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, _
      adCmdTableDirect

   ' 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
   rst.Close

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.

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.

Valli_an

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?

DrRyan3,

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.

Cheers.

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: http://support.microsoft.com/support/kb/articles/Q243/4/65.ASP

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"

Did you create a index for the table, with VeneName as the field for index?

VeneName should read as VenueName. - typo error.

Yes, I did created an index for VenueTable whose field is VenueName.

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.

Cheers.

Thanks a lot!