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