ADO FIND question

[Dennis]

I have a VB program accessing non-indexed tables (by primary key). I'm using
the RECORDSET.OPEN / RECORDSET.FIND syntax, coupled with RECORDSET.MOVENEXT.

I would like to be able to stop the quesry when the last record with my
specified value is found. How can I do that when the field I'm using is not
indexed (even though it's a key field)?

Any insight would be appreciated.

Thanks!

 

[Brendan Reynolds]

A primary key is an index.

I'm not sure that I understand the rest of your question. How is your code
supposed to know that the last record to match your criteria is the last
record without searching the remaining records? If you have ten records, and
records 3, 5 and 7 match your criteria, how is your code to know that record
7 is the last match without examining 8, 9 and 10, regardless of whether
there is an index or not? Or am I misunderstanding the question?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Dennis]

What I mean is, should I be using a different approach to the lookups? DoCmd,
Seek, etc. When I was a mainframe programmer, the FIND on a key (sorted or
not) would return ONLY those records that matched the key value, so no
additional lookups were needed. When you reached the "end-of-chain", you'd
processed all records with that key value.

Is there some method/sntax I can use to duplicate that type of result?

Thanks

 

[Brendan Reynolds]

Yes. Use a SQL statement to open a recordset containing only the required
records ...

Public Sub OpenRecordset()

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT * FROM Categories WHERE CategoryID >= 2 AND CategoryID
<= 5"
Do Until rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop
rst.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Jamie Collins]

Yes. Use a SQL statement to open a recordset containing only the required
records ...

That requires hitting the database each time.

It seems clear to me the OP is looking for the ADO Recordset property
Filter. To use it, you effective apply the WHERE clause to the
recordset, after which only the matching rows/records show and EOF may
be found as expected. You can then apply anther filter without going
back to the database.

Jamie.

--

 

[Brendan Reynolds]

It depends whether the original poster wants to select a single group of
records for processing, in which case I would go with selecting only those
records in the SQL statement, or select one group, process it, then another
group, process that, etc., in which case yes, .Filter may be a better
option. It is not clear to me from the original post that the latter is the
case, but certainly it might be.
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.