Results 1 to 5 of 5
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    How to track what users have logged in


    I would like to track what and when users have logged into the database.
    I am a complete novice with SQL server and just fumbling through as I learn. and I know its probably a bigger project than "go to this menu and click on this checkbox" but I was hoping someone could give me some tips or point me in the right direction.

    i am not opposed to setting up a table, and (hopefully) try to figure out how to make it track the logins.
    but it also seems like there is some sort of a .log file that I can create that will do it as well.

    any help is appreciated

    Thanks

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMO you do need tblLoggedIn. It would contain the userID from tblUsers (e.g. 22 - the autonumber value) and to make that easier to decipher, the Fname and Lname fields plus whatever else you need. A checkbox is minimal and doesn't tell you much. If you want an ongoing log use Now() for the date/time as DateTimeIn and DateTimeOut fields. When a user logs in your code would append a record and when they log out, update DateTimeOut. The drawback is that abnormal db shutdown by the user can leave DateTimeOut as Null so if you intend to control how many sessions a user can create you'll have that issue to work around. I don't think a log file is necessary if you have this in a table, but that's just my opinion. I might use a log file to flag general issues though. For that, research CreateObject("Scripting.FileSystemObject"). You can even give your file a .log extension and Notepad will open that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    user logging is a standard feature in SQL server: https://docs.microsoft.com/en-us/sql...l-server-ver15 . Besides that you have a lot af system views to track user activity. For example try exec sp_who2 to see who is logged in to what. Once you now the session ID, you can find the query they are running with:
    Code:
    DECLARE @sqltext VARBINARY(128)
    SELECT @sqltext = sql_handle
    FROM sys.sysprocesses
    WHERE spid = (YourSessionID)
    SELECT TEXT
    FROM sys.dm_exec_sql_text(@sqltext)
    GO
    So you see, you find it all within SQL server itself
    and they have the log files within SQL server, if not enugh: just start an extended event. No need to have log files outside SQL server

  5. #5
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    When a login happens, you want to trigger something like this:

    Code:
    INSERT INTO tblLogin (UserID, DateTimeLoggedIn) VALUES (Me.UserID, Now())

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

Similar Threads

  1. Replies: 11
    Last Post: 05-26-2021, 08:01 AM
  2. Replies: 4
    Last Post: 01-13-2015, 09:49 AM
  3. Auto fill Txtbox with logged in Users Name
    By pfales in forum Access
    Replies: 3
    Last Post: 04-30-2013, 01:22 PM
  4. Replies: 2
    Last Post: 07-18-2012, 08:14 AM
  5. Track users use of Access database
    By booyeeka in forum Programming
    Replies: 1
    Last Post: 02-26-2009, 02:35 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