Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User Login / Password log (Access 2000)

    Hi

    I am working on a small DB application and the project overseer would like to see a login page for the three users that will be using the application. The only thing I have thought of so far is to create a User table that stores the User ID and password, also a form to accept the user ID and password not sure what code to use to validate. The other requirement would be to maintain an activity log on the user. If the user is logged in and it's important to know when the users has changed a form, table or report. If someone could help I would really appreciate it. Unfortunately I am not a programmer but I am trying to learn. Thanks

    Sonya

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Login / Password log (Access 2000)

    The best way to secure an Access database is by implementing user-level security. It may seem daunting at first, but it really isn't that hard, especially if you use the Security Manager Wizard. Access moderator <!profile=WendellB>WendellB<!/profile> has a tutorial on security on his website (link in his profile, look for Support > Tutorials) with many useful links.

    Usually, end users are only allowed to enter and modify data; you can keep track of this by including a UserName (text) and Modified (date/time) field in each table, and setting this field in the BeforeUpdate event of forms bound to the tables. Letting users modify the design of forms and reports is not a good idea in a multi-user environment; in the first place, the database must be locked for exclusive use when the design of a form or report is being edited, in the second place, it easily leads to database corruption.

  3. #3
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Login / Password log (Access 2000)

    Hans

    I think I understand what you are asking me to do add two additional fields to table that drive forms. I am not sure how I get the userID value from the Security Manager. I am assume the date should populate anytime the user access the form. Something else I am not sure about can two people be in the application at the same time. Wasn't sure if the security would allow it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Login / Password log (Access 2000)

    If you have secured your database, users have to log in using a username/password combination. The CurrentUser function returns this login name.

    In an unsecured database, where users just open the database without entering a username/password, CurrentUser always returns Admin, so that is pretty useless to distinguish users. In that case, you could use the network login name instead - see <post#= 280661>post 280661</post#>.

    You could use code like this in the BeforeUpdate event of a form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Modified = Date ' or Now if you prefer to have the time too
    Me.UserName = CurrentUser
    End Sub

    This will automatically fill in the date (and time if you like) and the name of the current user when the record is saved after being modified.

    Access is a multi-user database; unless you explicitly otherwise, more than one user can open the database, and even the same form, at the same time. The data and username will be written into the record each of them modifies.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Login / Password log (Access 2000)

    In the other thread, you asked
    <hr>Having problems, not sure what's what! Have used the wizard and have set up three users whom all have Admins permissions, it works fine. I guess my question is how do I move the database to a more centralized location (S drive (server) where the application can be accessed at anytime by all authorized users. Also I need to know can all users be in the database at the same time?<hr>
    My reply above more or less answers these questions:
    - Have the users open the database through a shortcut that refers to the secured workgroup file.
    - Multiple users can open the database at the same time. Access has a theoretical limit of 255 simultaneous users, but in reality, you should stay well below that. There is no problem if ten or twenty or even thirty users open the database for viewing data only, but the number of users that edit and/or enter data simultaneously should be limited.

  6. #6
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Login / Password log (Access 2000)

    1st (Whe using the wizard I specify I want to create a shortcut)
    2nd (When completed here is the target pathe "C:Program FilesMicrosoft OfficeOffice10MSACCESS.EXE" "C:Newest3NVTasof8_05.mdb" /WRKGRP "C:Secured.mdw"

    I want my users to be able to access the database from the S drive. I make copy of Newest3NVTasof8_05.mdb along with the Secured.mdw and when the user clicks the DB opens right up or it gives a message you don't have permissions to open database. WHAT I AM NOT DOING CORRECTLY

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Login / Password log (Access 2000)

    Have you adapted the path to the database AND to the workgroup file in the shortcut after copying database and shortcut to S: ?

    If the database is working correctly on C:, but not on S:, perhaps you were using another workgroup file than you thought. In itself, if it works on C:, it should work on S: too if you adapt all paths correctly.

  8. #8
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Login / Password log (Access 2000)

    Finally got it, thanks!

  9. #9
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Login / Password log (Access 2000)

    You could use code like this in the BeforeUpdate event of a form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Modified = Date ' or Now if you prefer to have the time too
    Me.UserName = CurrentUser
    End Sub

    This will automatically fill in the date (and time if you like) and the name of the current user when the record is saved after being modified.

    Hans I want to make sure I understand how the tracking user by date works. I will add two fields to each table that control the form (User ID and Date). Or there a way to creat one new file that can track a user activity. ( BeforeUpdate of AfterUpdate ?)


    Thanks

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Login / Password log (Access 2000)

    If you want to track which user has last modified a record in a table, and when, add username and modified fields to that table, and populate the fields in the BeforeUpdate event of the form bound to that table. It must be done through a form, Access has no table-level triggers.

    You can also create a separate table within the database, and store username and date each time a user logs in to the database, or you could store this information in an external file, for example in a text file that acts as a log file. It's more complicated than the above, but it can be done in Access.

    If you want to keep track of all actions of a user in the database, things get quite complicated, and the log table(s) would grow very rapidly. If you really need that, Access is probably not the ideal database platform any more. Consider migrating the data to SQL Server.

  11. #11
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Login / Password log (Access 2000)

    Me.Modified is not recognized
    Me.UserName is not recognized either

    I added a text field UserID to every table and a field called date using date/time!

    Do I need to define variable to work with "Modified" & "UserName"


    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Modified = Date ' or Now if you prefer to have the time too
    Me.UserName = CurrentUser
    End Sub

  12. #12
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Login / Password log (Access 2000)

    Got it wasn't thinking had to add fields to form
    Me.userid = CurrentUser
    Me.Date = Date

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Login / Password log (Access 2000)

    Allthough this will work, it is not recommended to name a field "Date" - it is all too easy to confuse the field name with the VBA function Date, and if you're not careful, Access will get confused too.

    In general, it is wise to avoid giving a field the same name as a built-in function or property.

  14. #14
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Login / Password log (Access 2000)

    thanks will change name of table!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •