Solved

Searching a table with  SEEK

Posted on 2001-06-15
14
258 Views
Last Modified: 2010-05-02
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"
  Adodc1.Refresh


with adodc1.recordset
  .FIND search criteria
end with

What is the equivalent in using the SEEK command?

Thanks

0
Comment
Question by:rcbarot
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 6195470
Adodc1.RecordSource = "SELECT * FROM VenueTable Where VenueName = 'X' ORDER BY VenueName"
 
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6195471
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.

0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6196140
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.
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6196206
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?
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6196333
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.
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6196494
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).
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 6196514
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
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:rcbarot
ID: 6197542
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"
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6197600
Did you create a index for the table, with VeneName as the field for index?

0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6197601
VeneName should read as VenueName. - typo error.
0
 

Author Comment

by:rcbarot
ID: 6198315
Yes, I did created an index for VenueTable whose field is VenueName.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6199430
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.
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 100 total points
ID: 6199432
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)
0
 

Author Comment

by:rcbarot
ID: 6304873
Thanks a lot!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now