Results 1 to 7 of 7
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    How to restrict login from accessing database application after some time

    Please guy, can someone help me with the following:




    How to restrict login from accessing my database application
    one week after first login?


    My login process is as follows:


    i have a Table called Tbl_Users that holds username and password.


    Tbl_Users
    -------------
    UserID (pk)
    UserName,
    Password,
    FirstLoginDate


    On my login form i have 1 combox to select username, and 1 text field to type password; then one button upon which user clicks to login.


    I used the following vba to operates the login process:


    Code:
    Option Compare Database
    Private intLogonAttempts As Integer
    
    
    Private Sub CmdLogin_Click()
    'Check to see if data is entered into the UserName combo box
    If IsNull(Me.CboUsername) Or Me.CboUsername = "" Then
    MsgBox "You must select a User name.", vbOKOnly, "Required Data"
    Me.CboUsername.SetFocus
    Exit Sub
    End If
    
    
    'Check to see if data is entered into the password box
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
    MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
    Me.txtPassword.SetFocus
    Exit Sub
    End If
    
    
    'Check that value entered for password field matches whatever is inside Tbl_Users
    If Me.txtPassword.Value = DLookup("Password", "tbl_Users", _
    "[UserId]=" & Me.CboUsername.Value) Then
    UserID = Me.CboUsername.Value
    
    
    'When all checkings are correct then display something...
    MsgBox "Nice! You have successfully logged into the system", vbOKOnly, "Required Data"
    Else
    'When checking fails then display error message
    MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
    "Invalid Entry!"
    Me.txtPassword.SetFocus
    End If
    
    
    'When user types incorrect password 3 times database application will shutdown
        intLogonAttempts = intLogonAttempts + 1
        If intLogonAttempts > 2 Then
            MsgBox "You do not have access to this database.  Please contact the system administrator.", vbCritical, "Restricted Access!"
            Application.Quit
        End If
    End Sub


    Now, in order to solve my issue, i was thinking of the following logic:


    1 - Automatically record the first login date of a user in the column FirstLoginDate of table Tbl_Users.


    2 - Then perform some check on ActualDate and FirstLoginDate.
    For example, upon clicking on the login button, perform the following check:


    - if ActualDate > than lastLoginDate+7days then error message
    - if ActualDate < than lastLoginDate+7days then show Main Menu
    - if ActualDate = than lastLoginDate+7days then show Main Menu

    Can someone help me apply the above logic (vba code)?


    Or guide me toward more convenient way to restrict login from accessing my database application one week after first login?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    date math is done with DATEADD,

    Code:
    iDays = DateAdd("d",7,lastLoginDate)
    select case iDays
        case > 7
            msgbox "Error message"
            docmd.quit
        case else
            docmd.openform "fMainMenu"
    end select

  3. #3
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Thanks Raman.
    How to get the first login date auto recorded in the users table?
    Note: I want that date to be recorded only once (the first time the user logs in).

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Something like this should work


    Dim db as database
    Dim MySQL as string
    Set db= currentdb

    If isnull(firstlogindate) =true then

    Mysql = Update tblusers set firstlogindate = date() where username =" & chr(34) & cbousername & chr(34)

    Execute db.mysql

    Endif




    Sent from my iPhone using Tapatalk

  5. #5
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Hi Andy49
    I tried the code, but I get a vba error saying:
    Expected end of statement with the following line highlighted in red:
    Mysql = Update tblusers set firstlogindate = date() where username =" & chr(34) & cbousername & chr(34)

  6. #6
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Instead of SQL, use a query.
    it never gets the syntax wrong.
    docmd.openquery "qsMyQuery"

  7. #7
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    I don't know how to do that.
    Can you help with full code please?

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

Similar Threads

  1. Permitted users only, restrict database
    By Heathey94 in forum Security
    Replies: 21
    Last Post: 12-08-2016, 01:01 PM
  2. Accessing Database by multiple user at a time
    By prasadmng555 in forum Programming
    Replies: 3
    Last Post: 11-23-2016, 08:29 AM
  3. Replies: 5
    Last Post: 06-26-2013, 11:40 AM
  4. Replies: 1
    Last Post: 09-26-2011, 03:12 PM
  5. Accessing Another Application
    By searle in forum Programming
    Replies: 2
    Last Post: 03-24-2010, 08:17 PM

Tags for this Thread

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