Should I worry about the time needed to run DLookup on a session log table?

JMongi

JMongi

Active member
Local time
Yesterday, 21:40
Joined
Jan 6, 2021
Messages
802
One of my login functions checks for any outstanding sessions still running on another computer. I just realized as time "keeps on slipping, slipping, slipping...into the future..." that my log file will get longer and longer and thus my Dlookup might start taking too long to run.

tblLoginSessions
LoginID
UserName
LoginEvent
LogoutEvent
ComputerName
Notes

Code: 
Public Function SessionCheck() As Boolean
On Error GoTo ErrHandler
glProcName = "modUserControl Function SessionCheck"
'This function checks the session log for any open sessions from the user and handles any already open sessions.
'The default is to prevent the user from logging in so that the code must work correctly for login.

Dim strCriteria As String, strSQL As String

strCriteria = "UserName='" & modUserControl.DomainUsername & "' And LogoutEvent Is Null"
SessionCheck = False

Select Case True
    Case DCount("LoginID", "tblLoginSessions", strCriteria) = 0
        'No unclosed sessions
        SessionCheck = True
        
    Case DLookup("ComputerName", "tblLoginSessions", strCriteria) = modUserControl.ComputerName
        'User is logged in on the same computer
        'Close previous session
        strSQL = "UPDATE tblLoginSessions " & _
                " SET LogoutEvent =" & Now() & ", Notes = 'Abnormal Close' " & _
                " WHERE UserName='" & modUserControl.DomainUsername & "' AND LogoutEvent Is Null AND ComputerName='" & modUserControl.ComputerName & "';"
        Debug.Print strSQL
        'CurrentDb.Execute strSQL
        SessionCheck = True
    
    Case DLookup("ComputerName", "tblLoginSessions", strCriteria) <> modUserControl.ComputerName
        'User is already logged in on another computer
        FormattedMsgBox "User " & modUserControl.DomainUsername & " is already logged in at workstation " & _
            DLookup("ComputerName", "tblLoginSessions", strCriteria) & "      " & "@User " & modUserControl.DomainUsername & _
            " MUST logout from that computer before logging in again.            @", vbCritical, "Already Logged In"
        
    Case Else
        'Something unaccounted for has happened
        'Message about it
End Select

ExitHandler:
'Any additional code needed on error
Exit Function

ErrHandler:
Call ErrProcessor(glProcName)
GoTo ExitHandler

End Function

Should I be concerned? If so, what can I do about it?
 
providing the fields you are referencing in the dlookup criteria are indexed, should not be a problem. The issues with domain functions is when they are used in queries because they have to be executed for each row.
 
I don't think there's really anything to worry about. But if you're thinking of reducing the number of DLookups to gain a little speed, then you might consider eliminating some of what I am thinking may be unnecessary steps in your login checks. For instance, something similar to this pseudocode:
Code: 
Dim strWorkstation As String
strWorkstation = Nz(DLookup("ComputerName", "tblLoginSessions", strCriteria), "")
If strWorkstation = "" OR strWorkstation = modUserControl.ComputerName Then
    'Close (ALL) previous session(s)
    strSQL="UPDATE tblLoginSessions " & _
        " SET ...
        " WHERE LogOutEvent Is Null "
Else
    'User is already logged in on another computer
    FormattedMsgBox ...
End If
Just a thought...
 
Last edited:
providing the fields you are referencing in the dlookup criteria are indexed
Click to expand...
Thanks for the info. Do all fields used in a DLookup, Dcount, etc function need to be indexed?
 
One thing that jumps out for me is you might have to run four domain aggregate functions against the same row in the same table, each one of which will have to open it's own recordset to get the value you want. In a case like this I would just open one recordset with SQL like...
SQL: 
SELECT * 
FROM tblLoginSession 
WHERE UserName='" & modUserControl.DomainUsername & "' And LogoutEvent Is Null
If I was worried about speed, I would take that step first, and potentially reduce my disk read time by 75%.
 
Index the fields in your criteria.
 
@theDBugy - If I am remembering correctly, one of the benefits of Select Case True is that the rest of the cases are not resolved when a case is found true.

But, I could be wrong.
 
JMongi said:
@theDBugy - If I am remembering correctly, one of the benefits of Select Case True is that the rest of the cases are not resolved when a case is found true.

But, I could be wrong.
Click to expand...
I think you are remembering correctly, but take a look at your third Case statement, for example. You are executing two DLookups looking for the same information. Just saying...
 
It definitely could use some optimization. I'll look at it again.

The main question is about Dlookup, Dcount, etc on an ever expanding table of records.
 
JMongi said:
The main question is about Dlookup, Dcount, etc on an ever expanding table of records.
Click to expand...
Understood, and I did try to answer that question. As I said, in my humble opinion, you probably won't notice much difference in the speed of your login process for many years to come. But, that's just one person's point of view. Cheers!
 
@theDBguy - Thanks! didn't mean to sound snarky at all. :) I did have a tangential question...in your code suggestion you used Nz to encapsulate the dlookup. Reason?
 
JMongi said:
@theDBguy - Thanks! didn't mean to sound snarky at all. :) I did have a tangential question...in your code suggestion you used Nz to encapsulate the dlookup. Reason?
Click to expand...
DLookup() can return a Null value, which you can't assign to a String variable.
 
theDBguy said:
DLookup() can return a Null value, which you can't assign to a String variable.
Click to expand...
By the way, DLookup() can only return the value(s) from the first matching record. So, if a user was consistently having problems logging out, you may be only closing one of those previous sessions. Just a thought...
 
Ah, makes sense. A little snipping and pruning and modifying and we get the following code (still using Select Case True instead of an OR in an If/Then).

Edit: To address your other concern. IF this code is working correctly, there should only ever be one hanging session. But, even if there is, I plan on having a mainteance code to run from an admin console to check for these outliers.

Code: 
Public Function SessionCheck() As Boolean
On Error GoTo ErrHandler
glProcName = "modUserControl Function SessionCheck"
'This function checks the session log for any open sessions from the user and handles any already open sessions.
'The default is to prevent the user from logging in so that the code must work correctly for login.

Dim strCriteria As String, strSQL As String, strLastComputer
SessionCheck = False

strCriteria = "UserName='" & modUserControl.DomainUsername & "' And LogoutEvent Is Null"
strLastComputer = Nz(DLookup("ComputerName", "tblLoginSessions", strCriteria), "")

Select Case True
    Case strLastComputer = ""
        'No unclosed sessions
        SessionCheck = True
        
    Case strLastComputer = modUserControl.ComputerName
        'User is logged in on the same computer
        strSQL = "UPDATE tblLoginSessions " & _
                " SET LogoutEvent =" & Now() & ", Notes = 'Abnormal Close' " & _
                " WHERE UserName='" & modUserControl.DomainUsername & "' AND LogoutEvent Is Null AND ComputerName='" & modUserControl.ComputerName & "';"
        Debug.Print strSQL
        'CurrentDb.Execute strSQL
        SessionCheck = True
    
    Case strLastComputer <> modUserControl.ComputerName
        'User is already logged in on another computer
        FormattedMsgBox "User " & modUserControl.DomainUsername & " is already logged in at workstation " & _
            strLastComputer & "      " & "@User " & modUserControl.DomainUsername & _
            " MUST logout from that computer before logging in again.            @", vbCritical, "Already Logged In"
        
    Case Else
        'Something unaccounted for has happened
        'Message about it
End Select

ExitHandler:
'Since this function is a key protection to unauthorized access, any error should prevent access.
SessionCheck = False
Exit Function

ErrHandler:
Call ErrProcessor(glProcName)
GoTo ExitHandler

End Function
 
JMongi said:
Ah, makes sense. A little snipping and pruning and modifying and we get the following code (still using Select Case True instead of an OR in an If/Then).

Edit: To address your other concern. IF this code is working correctly, there should only ever be one hanging session. But, even if there is, I plan on having a mainteance code to run from an admin console to check for these outliers.

Code: 
Public Function SessionCheck() As Boolean
On Error GoTo ErrHandler
glProcName = "modUserControl Function SessionCheck"
'This function checks the session log for any open sessions from the user and handles any already open sessions.
'The default is to prevent the user from logging in so that the code must work correctly for login.

Dim strCriteria As String, strSQL As String, strLastComputer
SessionCheck = False

strCriteria = "UserName='" & modUserControl.DomainUsername & "' And LogoutEvent Is Null"
strLastComputer = Nz(DLookup("ComputerName", "tblLoginSessions", strCriteria), "")

Select Case True
    Case strLastComputer = ""
        'No unclosed sessions
        SessionCheck = True
       
    Case strLastComputer = modUserControl.ComputerName
        'User is logged in on the same computer
        strSQL = "UPDATE tblLoginSessions " & _
                " SET LogoutEvent =" & Now() & ", Notes = 'Abnormal Close' " & _
                " WHERE UserName='" & modUserControl.DomainUsername & "' AND LogoutEvent Is Null AND ComputerName='" & modUserControl.ComputerName & "';"
        Debug.Print strSQL
        'CurrentDb.Execute strSQL
        SessionCheck = True
   
    Case strLastComputer <> modUserControl.ComputerName
        'User is already logged in on another computer
        FormattedMsgBox "User " & modUserControl.DomainUsername & " is already logged in at workstation " & _
            strLastComputer & "      " & "@User " & modUserControl.DomainUsername & _
            " MUST logout from that computer before logging in again.            @", vbCritical, "Already Logged In"
       
    Case Else
        'Something unaccounted for has happened
        'Message about it
End Select

ExitHandler:
'Since this function is a key protection to unauthorized access, any error should prevent access.
SessionCheck = False
Exit Function

ErrHandler:
Call ErrProcessor(glProcName)
GoTo ExitHandler

End Function
Click to expand...
I like it. Good luck with your project.
 
Thanks! Getting my coding sea legs back under me again. :coffee::giggle:
 
You must log in or register to reply here.

Similar threads

JMongi
Public Variables in a module vs Dim variables in a form
Replies
17
Views
1,964
Pat Hartman
P
S
Solved How can I fix this code, so that the time data are logged to the CURRENT record an user is working on and NOT create a new entry??
Replies
14
Views
1,626
Pat Hartman
P
gakiss2
pass non related table value to a control
Replies
4
Views
432
theDBguy
theDBguy
S
Event Logger Module Need some help to add a small part
Replies
3
Views
3,025
smig
S
K
Dlookup or ? to compare windows login to a table of allowed accesses
Replies
3
Views
1,765
KyleB
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom