Seek & find methods 
Author Message
 Seek & find methods

Help!

I have a table that has a compound primary key.  The key
is made up of a string and a long.  Example "out" & 10011.

The following seek does not work where strStore
= "out10011"

dim strStore as string, strNo as string

with rst
   .index = "PrimaryKey"
   .CursorLocation = adUseServer
   .open "tblCustomer", conn
   .Seek strStore , adSeekFirstEq
end with

I have even tried  .Seek "xxx" & val(strNo)
The bottom line is how can I reference multiple fields
into the seek command?

I also tried the find command but ran into the same
problem, referencing multiple fields.

How can I put multiple fields into a find command?

Thanks for any help.

Dan



Sun, 16 Oct 2005 22:42:03 GMT  
 Seek & find methods


Quote:
> I have even tried  .Seek "xxx" & val(strNo)
> The bottom line is how can I reference multiple fields
> into the seek command?

Seek is really a {*filter*} sop to people who are not only old enough to
remember dBase but too old to change gear.

Simply use (a) the normal methods like

  FindFirst "FieldOne=""Eric"" and FieldTwo=""Martha"""

or else (b) do it the real way

  SELECT stuff, stuff, stuff FROM MoreStuff
  WHERE FieldOne="Eric
    AND FieldTow="Martha"

To my mind, it's a bit like Excel still using A1 addressing notation
instead of sticking with the cleaner and more rational R1C1.

B Wishes

Tim F



Mon, 17 Oct 2005 02:11:18 GMT  
 Seek & find methods

Quote:

>Help!

>I have a table that has a compound primary key.  The key
>is made up of a string and a long.  Example "out" & 10011.

Is this a two-field primary key? Or are you (redundantly) storing a
third field concatenating the two?

Quote:
>The following seek does not work where strStore
>= "out10011"

>dim strStore as string, strNo as string

>with rst
>   .index = "PrimaryKey"
>   .CursorLocation = adUseServer
>   .open "tblCustomer", conn
>   .Seek strStore , adSeekFirstEq
>end with

If it is a two field key, you must specify the values to be sought, in
the order that they're specified in the index: e.g.

Seek "out",10011

A ten field Index would have ten arguments separated by commas.

Quote:
>I have even tried  .Seek "xxx" & val(strNo)
>The bottom line is how can I reference multiple fields
>into the seek command?

>I also tried the find command but ran into the same
>problem, referencing multiple fields.

>How can I put multiple fields into a find command?

As suggested elsethread, the simplest way is not to use Seek or
FindFirst at all - instead, use a Query. Seek is useful if you have
local tables and require the fastest possible performance for
retrieving a record in VBA code... but it's not the only way, nor the
simplest way, nor the most flexible way to retrieve data!

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public



Mon, 17 Oct 2005 06:14:16 GMT  
 Seek & find methods


Quote:
> Seek is useful if you have
> local tables and require the fastest possible performance for
> retrieving a record in VBA code...

Presumably it's faster because it bypasses all the Rushmore optimisation
stuff?

Tim F



Tue, 18 Oct 2005 02:17:10 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Finding a record without Find/Seek methods...

2. Finding a record without Find/Seek methods...

3. Seek and Find Methods

4. Need Help with find or seek method

5. Find and Seek methods

6. ADO (No Seek Methods so use Find ?)

7. Seek method not found for my ADODB.Recordset object

8. SEEK Method/FindFirst Methods

9. ADO Find method & multi-criteria

10. ADO find method compare with DAO findfirst method...

11. Finding "FIND" method

12. Seek Method isn't working...

 

 
Powered by phpBB® Forum Software