Is there a faster alternative to DLookup?

Question

I'm using DLookup to search for a field in a table. It runs correctly, but is slow. Is there anything I can do to speed it up?

Here's my existing code:

Private Sub cmdLogin_Click()

strUserLevel = ""

If IsNull(Me.cmbUserName) Or Me.cmbUserName = "" Then
    MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
    Me.cmbUserName.SetFocus
    Exit Sub
End If

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
        MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
    Exit Sub
End If

'strUserName = cmbUserName.Value

If Me.txtPassword.Value = DLookup("Password", "tableUser", "[lngEmpID]=" & Me.cmbUserName.Value) Then
    lngMyEmpID = Me.cmbUserName.Value
    strUserLevel = DLookup("Department", "tableUser", "[lngEmpID]=" & Me.cmbUserName.Value)
    strUserName = DLookup("User_Name", "tableUser", "[lngEmpID]=" & Me.cmbUserName.Value)
    boolInventoryMDL = DLookup("Inventory", "tableDepartment", "[Department]=""" & strUserLevel & """")
    boolDispositionMDL = DLookup("Disposition", "tableDepartment", "[Department]=""" & strUserLevel & """")
    boolReviewCloseMDL = DLookup("Review", "tableDepartment", "[Department]=""" & strUserLevel & """")
    boolAdministratorMDL = DLookup("Administrator", "tableDepartment", "[Department]=""" & strUserLevel & """")
    boolUserListMDL = DLookup("UserList", "tableDepartment", "[Department]=""" & strUserLevel & """")
    boolUserLevelMDL = DLookup("UserLevel", "tableDepartment", "[Department]=""" & strUserLevel & """")

    If strUserLevel = "Superuser" Then
        MsgBox "Welcome back Superuser! You can access all the modules here..", vbOKOnly, "Caution"
    Else
        MsgBox "Welcome! Login Success!", vbOKOnly, "Login Page"
    End If
    DoCmd.Close acForm, "frmLogin", acSaveNo
    DoCmd.OpenForm "frmModule"

Else
    MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
    Me.txtPassword.Value = ""
    Me.txtPassword.SetFocus
End If

End Sub

Answer 1

I don't believe the problem is due to inherent slowness of DLookup. Rather the problem is that the code uses so many of them.

Open one recordset based on a query of tableUser and take the values you need from that recordset. Then open a second recordset from a query of tableDepartment and get your remaining values.

Dim db As DAO.database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSelect As String

strSelect = "SELECT u.Password, u.Department, u.User_Name" & vbCrLf & _
    "FROM tableUser AS u WHERE u.lngEmpID = [which_EmpId];"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSelect)
qdf.Parameters("which_EmpId") = Me.cmbUserName
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
If Not rs.EOF Then
    If rs![Password] = Me.txtPassword Then
        strUserLevel = rs!Department
        strUserName = rs!User_Name
        rs.Close

        ' open another recordset from a query of tableDepartment
        ' to retrieve your bool????? values

    End If
End If

In that abbreviated sample, I used a temporary QueryDef for the parameterized SELECT query. However you would be better of to save that SQL as a named query, perhaps qryFetchUserData. Then at run time, instead of recreating the query each time, you could simply open the saved query.

Set qdf = db.QueryDefs("qryFetchUserData")

For optimum performance, you should add indexes on tableUser.lngEmpID and tableDepartment.Department if they're not already indexed.