DAO Seek order

[InstructionWhich7142]

I need to seek on a non unique index, I have set the order with a select when I've opened the recordset, does seek definitely find the first occurance when using "=" ?

If comparison is less than (<) or less than or equal (<=), Seek starts at the end of the index and searches backward. However, if there are duplicate index entries at the end of the index, Seek starts at an arbitrary entry among the duplicates and then searches backward.

Because the above statement has me worried! I'm not searching backwards, but, can a start to finish seek be relied upon to work in the right order as this is critical! thanks

 

[GregRun]

I have set the order with a select when I've opened the recordset

You can only use Seek with a table-type recordset (dbOpenTable). As soon as you use SQL (i.e. your SELECT statement) it becomes a Dynaset-type recordset.

You can still use seek, but you would have to have all your data in a table and use the Sort Order in the Index settings in the table's design view. I use DAO quite a bit and I have had very few situations where Seek was even an option. I would suggest trying to find another way.

 

[InstructionWhich7142]

That's no problem, i'm only selecting * from a single table,

The only reason i was going to use SQL is that I was told even a table made by a "Make Table" query with a sort set, would not in fact be sorted, and that I should use a query when opening the recordset to achieve this, but it makes sense a sorted index would work the same,

thanks for the additional info i will add another index with a sort on it,



how would the Seek behave if there are two indexes, one with just the value i'm Seeking, and the other [the sort index] which is the same value with the date after it, i guess as i'm using "=" it would ignore the ones with the date appended to the end?

 

[GregRun]

Like I said, I don't use Seek that much so I don't know if there are any weird Access quirks with this but once you open the recordset you have to declare the index you are going to use (rst.Index = "Index1") and the recordset will be sorted based on that index.

 

[InstructionWhich7142]

oh, fair enough, i've already been declaring indexes for other Seeks so i should just be able to do that as well, wonder if i can have two indexes? (one for the sort and one for the seek)

think i might just have to set it up and do some testing!

cheers for the help, if anyone else knows of any oddities I would love to know for sure though

 

[gemma-the-husky]

i suppose it is saying that seek on a duplicate key may return any of the items matched by the seek

it is not clear then whether successive reads would retrieve all the records that would have been satisfied by the initial seek. (your quote makes it sound in fact that it may not)

 

[smig]

.findfirst will definitely find the first occurance

 

[GregRun]

.findfirst will definitely find the first occurance

I'm assuming that they are using Seek because it's much faster than findfirst. I was merely answering the question about the Seek method.

Whether Seek is the best thing to use here is unknown because we simply don't know enough about the base table. How many records are there? What will you be doing with the records once you've "seeked" them?

 

[InstructionWhich7142]

Gemma - I hope that the quote definitely only applies to backwards searches then! lol

There's a few thousand records in the table and it'll need to seek ~200k times. Seek was definitely chosen for speed!


So as long as I can use two indexes in DAO I'm ok (is this possible?)
I'd need to sort on one index (the unique one) and seek on the non unique one, I guess if I definine them one after another the last one (which would be the unique sort one) will be the one that the table ends up sorted by?

I'm going to have to make a test table and do some experimentation aren't I!

 

[GregRun]

I wouldn't rely on that to work... Any way you slice it, your process is not going to be instant. I would err on the side of caution and use FindFirst. I have a database that uses FindFirst about 500k times on a few thousand records and it takes about 3 or 4 minutes to run.