Using Seek with value array for an index index

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

Are you sure it's not just:

rst2.Seek field1, field2, field3, ...

? (I don't have Access here to check).

TC


Iain MacGranthin said:
You seem to have understood and reworded the question
well, so may be you can please answer it:

I am trying to lookup a value in a table, and I need more
the one condition for the search, because the index I am
using has more than 1 column.
Click to expand...

(snip)
 
I am trying to use seek on a recordset using the primary
key.
I have set up a variant array so that it contains the
values for each column in the primary key, in the same
order as the primary key.
When I try to run the module I get 'type mismatch' on the
array after the recordset seek statement:
Thwe pertinent code reads:

Dim RST2_Array(4) As Variant


Set dbs = CurrentDb
Set rst2 = dbs.Openrecordset("tabby", dbOpenTable)
RST2_Array(0) = element
RST2_Array(4) = rst.Fields("Sub IOT Item")
RST2_Array(2) = Arrow
RST2_Array(1) = Mess
rst2.Index = "PrimaryKey"
rst2.Seek RST2_Array

I cant find a good example of how to do this, so can
anyone help?
 
Have changed seek line to:
rst2.Seek RST2_Array(0), RST2_Array(1), RST2_Array(2),
RST2_Array(3), RST2_Array(4)

and even added 'adSeekFirstEQ' in various different
palcers but still no joy...
 
The line of code you have of

rst2.Seek RST2_Array

The above is non sense. I can't figure out how (or why) you can assume that
a array can be used for the value of a condition?


The format of seek is:

rst2.Seek " your condition", "key value"

So, if you are looking for a key id value of 123, you would use

rst2.Seek "=", 123

Also :
values for each column in the primary key, in the same
order as the primary key.

Are you saying that you already loaded up the array, or are you trying to
load up the array? I can't again figure out if you already did load up the
array (and further why load up an array, when you can just fill a reocrdset
via sql?). Why can't you just use an reocrdset here?


Also, "Arrow" and "Mess", where are they defined?

Are you perhaps trying to grab some values from the table?

Lets take another shot at this...because there too much left out here.
Perhaps just a one sentence like I am trying to lookup a value in a table,
and I need more the one condition for the search?
 
You seem to have understood and reworded the question
well, so may be you can please answer it:

I am trying to lookup a value in a table, and I need more
the one condition for the search, because the index I am
using has more than 1 column.

ps Your comments are a bit over dry though I understand
why you might need to make them.

As it happens the Microsoft Access help suggests an array
can be used, and as I know that complex keys can be made
by concatenating fields, I saw no reason why a method
might not want to do it by assigning values to an array
then matching the array values to the stated index.

Actually programmers do it all the time, with a parameter
being filled by an array overlay.

Can you see how that might work, or are you just wanting
to insult me?

I didnt assume anything more than try to understand the
help example, but did my best with the really poor quality
help that was available through the software.

Once that didnt work I came here. If it looks a mess then
maybe you should progress it with Microsoft as a
reflection on the quality of their help files.
 
You seem to have understood and reworded the question
well, so may be you can please answer it:

I am trying to lookup a value in a table, and I need more
the one condition for the search, because the index I am
using has more than 1 column.
Click to expand...


Lets try:

dim strSql as string
dim rstResult as dao.recordset

strSql = "select [Sub IOT Item] from tabby " & _
"where Somefield = 'Arrow' and SomeOtherField = 'Mess'"

set rstResult = currentdb.OpenRecordset(strSql)

if rstResult.recordCount > 0 then
msgbox "there is a match
else
msgobx "there is no match"
endif

As mentioned, it is not clear of where/when Arrow, and mess are set. Are
these set somewhere?
 
They were key values declared as strings assigned from
another table.

Thanks for the solution: Actually when I saw your example
for using 'seek' I tried it out with each key value as
part of a list:

rst2.seek "=",val1,val2,val3

And it worked OK, and it bears no relation to the examples
given by visual basic help in access!

From a performance point of view, I was using seek because
I thought it may be more efficient than processing a fresh
sql statement for each record in a module that runs
through a table looking for new values to add to another
table.

Is that a fair assumption?

-----Original Message-----
You seem to have understood and reworded the question
well, so may be you can please answer it:

I am trying to lookup a value in a table, and I need more
the one condition for the search, because the index I am
using has more than 1 column.
Click to expand...


Lets try:

dim strSql as string
dim rstResult as dao.recordset

strSql = "select [Sub IOT Item] from tabby " & _
"where Somefield = 'Arrow' and SomeOtherField = 'Mess'"

set rstResult = currentdb.OpenRecordset(strSql)

if rstResult.recordCount > 0 then
msgbox "there is a match
else
msgobx "there is no match"
endif

As mentioned, it is not clear of where/when Arrow, and mess are set. Are
these set somewhere?


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.
Click to expand...
 
From a performance point of view, I was using seek because
I thought it may be more efficient than processing a fresh
sql statement for each record in a module that runs
through a table looking for new values to add to another
table.

Is that a fair assumption?
Click to expand...

Yes, you are most correct. If you are not looping, then there is really
going to be no difference between the seek, and just using a whole sql
statement. The query processor is smart, and will using the index fields
correctly for you.

Note that if you are calling this process over and over, then seek will ONLY
be faster *if* you keep the reocrdset open. The performance hit is in
opening the table and creating the reocrdset, not the speed at which the
record is retrieved! (it is kind of like starting your car in the morning
before you get going!). That recordset creating is a real heavy hit. If you
are just doing one seek, then the sql example would be just as fast since in
both cases you suffer this HEAVE HIT.

However, if you have bunch of values, then possibility a sql statement can
be generated that returns that whole data set you are checking for anyway.
That will be MUCH faster then looping and using seek.

Regardless, you will have to remove the recordset creating out of the
looping (ie: only create the recordset once at the start). Of course, using
sql each time means you can't keep the recordset open!

And, if I sounded it a bit harsh in my original answer, my apologies. It was
my lack of grasping your question that provoked such a "what the heck??"
kind of remark on my part!. That was un-called for in my case! Sorry...

A few more things:

Seek is a very old legacy command in ms-access. It is not supported in odbc,
or even for sql server. Further, if you split your database into two parts
(*highly recommend*), then seek will also not work correctly (you can make
it work in a linked table...but it is more messy). So, I would give some
hard thought on how to avoid the seek if at possible (especially due the it
not working on linked tables).

If you not familiar with splitting...here is some read in this:

http://www.granite.ab.ca/access/splitapp.htm

http://www.microsoft.com/accessdev/articles/bapp97/chapters/ba15_3.htm
 
Thanks:

I am actually keeping the rst2 recordset open while
looping through the recordset (rst1) that contains values
for the seek: the seek is used about 600 times at the
moment, and a new record added to rst2 recordset when
nomatch is true.

At first I tried Findfirst, but access told me this method
was invalid for the recordset. So I moved on to seek.

However, if it is a legacy method what should I be using?
As I am looping, I dont really want to run an sql query to
create a new result set every time - even though it may be
only 600 occurrences, its not so elegant (I also have a
background in mainframe batch processes, where overheads
can accumulate, so you go for the most efficient method as
a habit)...
 
access help suggests this with a constant on the end to
indicate what kind of find you want, but it doesnt work
like that - the help file is poor: what does work is
seek "=" val1,val2,val3,...
 
iain macgranthin said:
Thanks:

I am actually keeping the rst2 recordset open while
looping through the recordset (rst1) that contains values
for the seek: the seek is used about 600 times at the
moment, and a new record added to rst2 recordset when
nomatch is true.
Click to expand...

yes, note that I was stating that using sql each time would NOT be so bad IF
YOU could keep the rocordset open. (but you can't, ....because each time you
are stuffing a new sql statement and a new recordset gets created). As
mentioned, it is all the time to "connect" to the table, and setup the
reocrdset that is the largest performance hit.

At first I tried Findfirst, but access told me this method
was invalid for the recordset. So I moved on to seek.
Click to expand...

FindFirst should work, (and, it works on linked tables). FindFist needs a
full "where" clause like sql.

strWhere = "City = 'Edmonton' and Country = 'Canada'"

rstRec.FindFirst (strWhere)
if rstRec.FindFirst.NoMatch = true then
msgbox "not found"
else
msgobox "found one"
endif

So, the advantage of findfirst is that the recordset stays open (I can't
stress how costly the opening is in terms of performance). The disadvantage
is that findfirst DOES NOT take advantage of indexing! So, findfirst don't
use the indexing well at all. If findfirst/findnext used indexing, this
would be no brain answer (I would say dump seek, and using
findfirst/findnext..but it don't use indexing!). In fact, if your table is
less then 3,000 records, then findfirst would be ok. If the table is larger,
then again too much scanning of the table will occur and slow things down.
So, the ideal is using some sql statement to ONLY return the needed records
(but, this may not be possible...and thus your need to "loop".
However, if it is a legacy method what should I be using?
As I am looping, I dont really want to run an sql query to
create a new result set every time - even though it may be
only 600 occurrences, its not so elegant (I also have a
background in mainframe batch processes, where overheads
can accumulate, so you go for the most efficient method as
a habit)...
Click to expand...

Yes, I can't agree more with the above. So, if the table is to be small
(less then 2000 records, then findfirst/findnext is ok). For larger data
sets, you either have to come up with some kind of sql statement here
(perhaps a join statement to return matches between the two tables that
match...that BY FAR the fast way).

If you can't use a sql join to match/compare values between the two tables,
then you are stuck (that in my humbale opiion is the real soltion here). You
will have to make the comprise to use findfirst. Or, if you must, continue
to use the seek command. However, eventually, you will want to split your
mdb. You can use the following workaround to use the seek command in linked
tables. So, if no way to use sql for the matching, then I would continue to
use the seek.

All the below link says is that you can open the linked mdb file by a full
path name, and then still use seek. Note if you have not split you mdb
file...it does not apply!)

http://www.mvps.org/access/tables/tbl0006.htm
 
I have written the routine to check 7 flags, which exist
on the record and represent 3 groups of related
conditions:
each flag from each group is tested against a flag from
each of the other groups, and when a true is found across
the 3 groups, an 'event occurrence' record is written.
Hence a single record from the first table can create up
to 12 records in the second table.
The variables mess, arrow, and element contain names for
the flags in each 'group' on the record, and the 3 arrays
that hold these names (arroway, messaray, elarray) are
looped so each flag is tested.

I dont know why findfirst didnt work, but it was
rejected... Here is the (revised and working) essentials
of the code - I have kept the rst2_arrayn as a way of
formally passing over the values to the method:

rst.MoveFirst
Do While Not rst.EOF
For i = 1 To 3
element = elarray(i)
For j = 1 To 2
Arrow = Arroway(j)
For k = 1 To 2
Mess = Messarray(k)
If rst.Fields(element) = True And
rst.Fields(Arrow) = True And rst.Fields(Mess) = True Then
' All true - Test for Existing Record
RST2_Array(0) = element
RST2_Array(1) = Mess
RST2_Array(2) = Arrow
RST2_Array(3) = rst.Fields("ref1")
RST2_Array(4) = rst.Fields("Item")
rst2.Index = "PrimaryKey"
rst2.Seek "=", RST2_Array(0), RST2_Array
(1), RST2_Array(2), RST2_Array(3), RST2_Array(4)
If rst2.NoMatch Then
' Add if not there already
rst2.AddNew
rst2.Fields("ref1") = rst.Fields
("ref1")
rst2.Fields("element") = element
rst2.Fields("Item") = rst.Fields
("Item")
rst2.Fields("Route") = Arrow
rst2.Fields("Type") = Mess
rst2.Update
End If
End If
Next k
Next j
Next i
rst.MoveNext
Loop

At this point thanks for your help is due. I will have
another crack at the findfirst, in case I got the syntax
wrong...
 
And I now have findfirst working with the recordset
as 'dbOpenDynaset' (previously it was dbOpenTable), though
the string is a bit long.....


Thanks!!!! - though it still leaves me wondering why
Access doesnt have a current method that exploits the
indices...
 
TC said:
I forgot the "=".

Isn't it ridiculous syntax!
Click to expand...

Isn't it a ridiculous function -- perhaps what the Access team likes to
think of as a bit of archeology?

Really, the .Index and .Seek methods should have been dropped a decade ago,
along with the file managers like dBase and FoxPro where they were
useful... but like AutoFill and LookUpTables they keep coming back.

Tim F
 
Back
Top