Results 1 to 7 of 7
  1. #1
    philipd is offline Novice
    Windows Vista Access 2003
    Join Date
    Nov 2009
    Location
    Belgium
    Posts
    3

    Logfile of what users do in database

    How can i setup my database so that there will be created a logfile (of in a table) what different users do in my database.


    For instance, editing, viewing, ...

    Any help is greatly appreciated.

    My database is in access 2003, it already has the security from office (user and group permissions)
    If there's some info that should be required, don't hesitate to ask

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Access does not provide this functionality you have to write it yourself. You have to determine what needs to be logged and by whom.

    David

  3. #3
    philipd is offline Novice
    Windows Vista Access 2003
    Join Date
    Nov 2009
    Location
    Belgium
    Posts
    3
    Quote Originally Posted by dcrake View Post
    Access does not provide this functionality you have to write it yourself. You have to determine what needs to be logged and by whom.

    David
    Do you have any idea where i should/could start?
    If i could log when people log in and when that they change something in the database, i would be very happy

    thanks in advance

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    You need to consider each DSSP, Do Something Significant Point.

    From an administrators point of view think of all the things that can/will take place in your system, and ask yourself "Would I like to know about that?" For Example when
    A user logs on
    What was their log in name
    What date/time was it
    Where did they log in from (PC)

    Record creation
    Who created it
    Date/Time created
    Which PC

    Record Amendmants
    Who amended it
    Date/Time Amendment
    Which PC

    Etc, Etc, Etc.

    These are all classed as DSSP's. So if a record gets deleted you can look at your audit trail and trace it back to the person who did it, date/time, which machine, etc.

    You also need to think about RBAC - Role Based Access Control - individual user permissions. If a user attempts to do something that they do not have acces to do then this needs recording. So if a minion tries to perform an Admin task you can log this as well.

    David

  5. #5
    philipd is offline Novice
    Windows Vista Access 2003
    Join Date
    Nov 2009
    Location
    Belgium
    Posts
    3
    Thx for the tips...

    I already have a number of users, and i also limited their abilities to the database. For instance, they're not able to edit the forms, delete records. Certain forms are not open for them to watch. In that aspect my database is pretty safe (90%)

    I also have a good idea, thanks to your tips, what sort of data i want to store in a log file.

    The only question remains how i can store the data in the access database, of text file.

  6. #6
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Ok lets say someone deletes a record from the table. Firstly don't let them phyically delete it. Place a deleted flag in the table and set it to True. (you can then retreive it should it be deleted in error, or purge as an admin task later).

    You have a Audit log table that holds certain info

    AuditId
    Audit Date/Time
    User Name
    Computer Name
    Audit Short Desc
    Audit Long Desc


    At the point the the user click on the delete button you would do necessary to the actual record then call the Update Audit log routine

    Add a record to the audit log
    Audit Id - Autonumber
    Audit Date - Now()
    User Name - Users Login name
    Computer - Which computer the user was logged into at the time
    Audit Short Desc - Deleted Sales Transaction
    Audit Long Desc - Deleted sales transaction for Invoice 111 Line 3

    This could be done as an append query or using recordsets.

    David

  7. #7
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    this topic also relates to user level security; the feature goes away in Access07.accdb format and we should assume the ongoing reality for Access in general. So both user logging (never an Access feature) and user level security are going to be a roll-your-own scenario.

    its pretty easy to have a user table with an ID and Level value; such that when they log in they can or can not see certain command buttons which lead them to specific forms/reports etc. it is also pretty easy to write an entry into a running log table as to whom logs in and what forms/report they open. But it all needs to be approached with the understanding that this is ok for casual users that are not tech savvy and not hacker oriented.

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

Similar Threads

  1. How to have multiple users
    By Eric1066 in forum Access
    Replies: 5
    Last Post: 11-19-2009, 05:14 AM
  2. sharing database over several users issue
    By marianne in forum Access
    Replies: 8
    Last Post: 06-02-2009, 07:20 PM
  3. Users on Database Simultaneously
    By rtemple in forum Access
    Replies: 2
    Last Post: 03-26-2009, 08:38 PM
  4. Track users use of Access database
    By booyeeka in forum Programming
    Replies: 1
    Last Post: 02-26-2009, 02:35 AM
  5. Multiple users accessing same database
    By 4ACE in forum Access
    Replies: 0
    Last Post: 02-28-2008, 03:10 PM

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