Results 1 to 5 of 5
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Dlookup versus Seek method DAO

    I have an app that performs its functions within a blink of an eye, that is until I move the backend to an NAS drive on the LAN. The "blink of an eye" became more like 45 seconds or so. I've isolated the section of code that is eating up all the time. One of the characteristics of the code is the extensive number of DLookups as controls on a form are populated under current conditions. Intuitively, I would imagine that a DAO Recordset when opened would be in "local memory" and seeks based on the primary record key would give me back the speed. Is my thinking correct with this situation?



    Here's the code if it helps to explain:
    Code:
    Private Sub lblCtls(flr As Integer)
    Dim intAptNum As Integer
    Dim intlblIDs As Integer
    Dim varPetName As Variant
    
    For Each ctl In Me.Controls
        If ctl.ControlType = acLabel Then
            If IsNumeric(right(ctl.Caption, 3)) Then       'Of the form 0nn
                intlblIDs = right(ctl.Caption, 2)          'Apt number at each floor
                intAptNum = intlblIDs + flr * 100          'Apt number specific to current floor
                ctl.Caption = intAptNum                    'And that's what we want to display
                
                'Me.Controls("t" & intlblIDs).top = ctl.top + ctl.Height   'Tuck it up under the apt number
                
                If Nz(DLookup("Unit", "QRegistry", "Unit = " & intAptNum)) = 0 Then
                    ctl.BackStyle = 0
                    ctl.BorderColor = 0
                Else
                    If DLookup("RegAs", "QRegistry", "Unit = " & intAptNum) = 4 Then
                        ctl.BackColor = lngDBkColor
                    Else
                        ctl.BackColor = lngAssignedColor
                    End If
                    
                    'Okay, now set the labels under the apt number
                    'Me.Controls("t" & intlblIDs).Visible = False  'Hide the floor-plan label
                    Me.Controls("n" & intlblIDs).Caption = _
                                DLookup("LastName", "QRegistry", "Unit = " & intAptNum)
    
                    varPetName = DLookup("Pet1", "QPets", "AptNum = " & intAptNum)
                    varPetName = varPetName & DLookup("Pet2", "QPets", "AptNum = " & intAptNum)
                    If Not IsNull(varPetName) Then Me.Controls("n" & intlblIDs).ForeColor = DarkGreen
                    
                    Me.Controls("n" & intlblIDs).Visible = True
                End If
            End If
        End If
    Next ctl
    
    Set ctl = Nothing
    
    End Sub

  2. #2
    wvmitchell is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    24
    You have three lookups for QRegistry, and two lookups for QPets... if you use DAO recordsets you only have to open two recordsets so that should more efficient. I would try it that way & time-test the results. HTH

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    that should more efficient
    That was my thought as well and I'll modify the sub accordingly.
    Thanks,
    Bill

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    if you use DAO recordsets you only have to open two recordsets so that should more efficient.
    Or one recordset if you can join on IntAptNum.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Using rs.FindFirst method worked just fine on DAO recordset(s) and an acceptable performance has returned. The Seek method is not supported on other than local tables.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-26-2017, 08:14 AM
  2. A method other than DLookUp
    By Abedecain in forum Queries
    Replies: 2
    Last Post: 01-08-2013, 09:15 AM
  3. DLookup Customer Ship method
    By burrina in forum Forms
    Replies: 2
    Last Post: 12-17-2012, 09:20 AM
  4. Seek Method
    By worldwidewall in forum Access
    Replies: 2
    Last Post: 03-08-2012, 02:46 PM
  5. dlookup versus SQL value search
    By marianne in forum Access
    Replies: 3
    Last Post: 07-15-2009, 09:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums