does DAO.FindFirst use table indexes?

[selahlynch]

I have been using the DAO.FindFirst function in order to find records in my database but it seems to be very slow. I am confused because it seems to perform at the same speed regardless of the indexes on my table.

That is...

rs.FindFirst (tape = 160 and ffid = 32451 and sline = 2286)

Performs at the same speed regardless of whether have indexes on tape, ffid, and sline or whether I have no indexes at all.

Is this to be expected? Or is something strange going on?

 

[gemma-the-husky]

if you do this often then you need an INDEX including those three fields.

then access will decide it can search using that index. If not, it has to search every record (probably in PK order) testing every record until it finds a match or gets to eof.

That is why it is slow.

------------
Seek is optimised, becasue you tell it which index to use, but seek cannot be used on a linked table. (although there are wrok rounds for this)

 

[selahlynch]

As I said before, I tried defining an index for those three fields and it did not speed up my DAO.RecordSet.FindFirst function at all. This is strange I think??

However, I tried it again using DAO.RecordSet.Seek and an appropriate index. This time it worked wonderfully. So problem solved. Thanks.

 

[selahlynch]

A snippet of code for future forum readers:

Dim db            As DAO.Database
    Dim rs            As DAO.Recordset
    
Set db = CurrentDb()
    Set rs = db.OpenRecordset("RawShot", dbOpenTable)
     
' index UniqReq(jd, sline) is defined in my access table 
rs.Index = "UniqRec"  

' ... define variables jd and sline

rs.Seek "=", jd, sline

' ... do something ...

rs.Close

 

[JANR]

I tried it again using DAO.RecordSet.Seek and an appropriate index. This time it worked wonderfully.

Did you get Dave's warning:

but seek cannot be used on a linked table. (although there are wrok rounds for this)

JR

 

[selahlynch]

Yes I did see that warning... but lucky for me, I'm not working on a linked table.

I will keep it in mind for the future though.

Thanks.

 

[DCrake]

By the sound of it you are not using a linked table, this suggests that your database is not split. Even if the system is a simgle user system it is always best to have a FE/BE setup.

Because you commented on the speed - or lack of it - also suggests your are dealing with a table with a large amount of records, this is another indication that your setup is wrong.

 

[selahlynch]

Well... one step at a time.

If you have any ideas about why FindFirst would not be utelizing my table indexes that would be helpful.

 

[gemma-the-husky]

Access decides its own method for using searches - I dont think you can force it to use a particular index in find first operations, as you can with a seek method

but what are you searching - a table or a query?

if you use a query for your recordset, sorted by those 3 fields (instead of searching the table)- then it may work a lot better.

and indeed if you expect to find just a single record then design your query to just return the 1 record. Then if the rs count is 0, it wasnt found - if its 1, it was found.

there are many ways to skin this cat.