Efficient/Correct Use Of FindLast

[twoplustwo]

Hi guys,

I have just read an article in the Wrox Access VBA 2003 book stating that SEEK is way better than FINDLAST if applicalble to your project.

Below is the code I currently use to open a recordset relating to a customer and check the last instance of correspondance.

Set rsC = db.OpenRecordset("qryContactActions", dbOpenDynaset) 'Set status
    rsC.FindLast "CustomerID=" & lngCustomerID
        If rsC!ActionType = 1 Then
            lblEmail.Caption = "Email sent"
            lblEmail.ForeColor = vbBlue
            lblEmail.FontSize = 8
        ElseIf rsC!ActionType = 2 Then
            lblEmail.Caption = "Email received from site"
            lblEmail.ForeColor = vbBlue
            lblEmail.FontSize = 8
        ElseIf rsC!ActionType = 3 Or rsC!ActionType = 4 Then
            lblEmail.Caption = "Telephone contact made, see notes"
            lblEmail.ForeColor = vbBlue
            lblEmail.FontSize = 7
        ElseIf rsC!ActionType = 6 Then
            lblEmail.Caption = "No further action"
            lblEmail.ForeColor = vbBlue
            lblEmail.FontSize = 8
        Else: lblEmail.Caption = "Email not yet sent"
            lblEmail.ForeColor = vbRed
            lblEmail.FontSize = 8
        End If
Set rsC = Nothing

It does work, I am just curious if there is anything I am missing. The db is split and located on a server, with each member of the team having a FE on their desktop.

 

[gemma-the-husky]

seek (indexed read) is faster, but isnt available in connected (ie back end) databases - to use it, you have to open the back end database in code - doable, but slightly harder

find operations are probably adequate for all but very large datasets

dont know if its faster or not, but you could sort the query descending, and then use findfirst to achieve the same objective

 

[twoplustwo]

Hi Gemma,

I sorted the query and implemented FindFirst and it does indeed seem a bit quicker! I assume FindFirst doesn't load the entire recordset?

It's not a large application but I wanted to make sure I'd applied the correct technique!

 

[gemma-the-husky]

i presume find last has to do repeated reads until it finds the last one, whereas findfirst just does one read - depending on the size of the dataset, it must be more efficient