How can you tell if a user is logging in using Access

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • anoble1
    New Member
    • Jul 2008
    • 246

    How can you tell if a user is logging in using Access

    I am trying to set up my databases to where I can tell who is logged in. I really don't have a good idea of how to do it. Right now I do have a Rights table with all the users in it. I just added a column which is either on or off called (SingnedIn)

    I am trying to figure out how; whenever a user pulls my database up, it will put change the "signedIn" field to True for that user. I have made a hidden box on the main screen that grabs the users NTID name. I just need a little help on how to tie that to the tableRights.

    Can anyone help?
    Thanks
  • TheSmileyCoder
    Recognized Expert Moderator Top Contributor
    • Dec 2009
    • 2322

    #2
    Use a hidden(Visible= false) form frm_Startup, where you have the following code, and set the form to open at startup.
    Code:
    Private Sub Form_Load()
      'Set user status=Logged in
      Dim strSQL as String
      strSQL="Update tbl_Users SET SingnedIn=TRUE WHERE tx_UserInitials='" & Environ("UserName") & "'"
      Docmd.SetWarnings false
        Docmd.RunSQL strSQL
      Docmd.setwarnings True
    
      'Now open the form you want user to see when he opens the database
      Docmd.OpenForm "frm_Menu"
    End Sub
    And in the same form, add code to the forms Close event,

    Code:
    Private Sub Form_Close()
      'Set user status=Logged out
      Dim strSQL as String
      strSQL="Update tbl_Users SET SingnedIn=FALSE WHERE tx_UserInitials='" & Environ("UserName") & "'"
      Docmd.SetWarnings false
        Docmd.RunSQL strSQL
      Docmd.setwarnings True
    
    End Sub

    Comment

    • anoble1
      New Member
      • Jul 2008
      • 246

      #3
      Originally posted by TheSmileyOne
      Use a hidden(Visible= false) form frm_Startup, where you have the following code, and set the form to open at startup.
      Code:
      Private Sub Form_Load()
        'Set user status=Logged in
        Dim strSQL as String
        strSQL="Update tbl_Users SET SingnedIn=TRUE WHERE tx_UserInitials='" & Environ("UserName") & "'"
        Docmd.SetWarnings false
          Docmd.RunSQL strSQL
        Docmd.setwarnings True
      
        'Now open the form you want user to see when he opens the database
        Docmd.OpenForm "frm_Menu"
      End Sub
      And in the same form, add code to the forms Close event,

      Code:
      Private Sub Form_Close()
        'Set user status=Logged out
        Dim strSQL as String
        strSQL="Update tbl_Users SET SingnedIn=FALSE WHERE tx_UserInitials='" & Environ("UserName") & "'"
        Docmd.SetWarnings false
          Docmd.RunSQL strSQL
        Docmd.setwarnings True
      
      End Sub
      Ok, here is my code. it looks right to me.

      I am getting an error message when I open the database. It throws up the SignedIn with a textbox. Then it says "Cannot update 'SignedIn'; field not updateable.

      Code:
         'Set user status=Logged in
      Dim strSQL As String
      strSQL = "UPDATE tblReviewNames SET SingnedIn = True WHERE (((tblReviewNames.NTID)='" & Environ("UserName") & "'));"
      DoCmd.SetWarnings False
         DoCmd.RunSQL strSQL
      DoCmd.SetWarnings True

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32634

        #4
        Whenever a user logs in add their name (ID) to your table. On closing the database have an event routine that removes it. If it is possible for a user to be on multiple times at once then include station (PC) details in the record.

        Comment

        • anoble1
          New Member
          • Jul 2008
          • 246

          #5
          Originally posted by anoble1
          Ok, here is my code. it looks right to me.

          I am getting an error message when I open the database. It throws up the SignedIn with a textbox. Then it says "Cannot update 'SignedIn'; field not updateable.

          Code:
             'Set user status=Logged in
          Dim strSQL As String
          strSQL = "UPDATE tblReviewNames SET SingnedIn = True WHERE (((tblReviewNames.NTID)='" & Environ("UserName") & "'));"
          DoCmd.SetWarnings False
             DoCmd.RunSQL strSQL
          DoCmd.SetWarnings True
          Got it to work! Works great! I messed up the query but works perfect now.

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32634

            #6
            Environment variables are fine up to a point, but they are not very secure, as anyone can change this to reflect a different user name after logging in. See Retrieve User ID for a more secure method.

            Comment

            • DataAnalyzer
              New Member
              • May 2010
              • 15

              #7
              If you want to see who's actually in a database at any given time and when they go in and out of the database, you may want to take a look at purchasing a predesigned package to do that.
              Last edited by MMcCarthy; May 11 '10, 05:22 AM. Reason: remove link to product

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32634

                #8
                I expect a package, especially one you have to pay for, would be overkill for such a simple situation. This is not rocket science.

                Comment

                • DataAnalyzer
                  New Member
                  • May 2010
                  • 15

                  #9
                  if you need to handle connections to the database that's outside your application, it's not such a trivial problem. You'll need something to monitor it independent of your application.

                  Comment

                  • NeoPa
                    Recognized Expert Moderator MVP
                    • Oct 2006
                    • 32634

                    #10
                    I don't believe that to be true. Is there anything that could support that contention? Especially bearing in mind that a viable solution has apparently been found to work?

                    Comment

                    Working...