Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Security on access (access 200 and up)

    Hello, I would like to create an Access database where i can setup a sort of security so that i can see who is changing witch field. Has anybody had any experience with this. Or is this simply not possible. Thanks for all your help.

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

    Re: Security on access (access 200 and up)

    If you want to set up a change log that tracks changes to every field, Access is probably not the ideal tool. It would soon become cumbersome. I think SQL Server has better tools for that, but others can tell you more about that.

    If you want to get a general idea of security in Access, take a look at moderator <!profile=WendellB>WendellB<!/profile>'s website. You'll find a link in his profile. On his website, look for Support, then Tutorials. "The Secrets of Security" provides a short introduction, and it contains lots of useful links.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Security on access (access 200 and up)

    Adding to Hans' response, Access works fairly well at tracking who did what / when if you are using forms to track things, but if you are allowing edits at the table or query level, then security doesn't really help. If you need to track changes at the field level, you will want to work with SQL Server or another database server that supports triggers. Triggers work at the table level, and fire any time a record is edited, deleted, etc. The basic concept is to create an archive table which shows the table before a record was edited. That way you can compare the current record with what it previously contained, and at the same time see who made the edit and when it was made. Post back if you want to explore this in more depth.
    Wendell

  4. #4
    New Lounger
    Join Date
    Dec 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Security on access (access 200 and up)

    Would be cool if you could give me more info on this. It is possible to block the table itself and make the users use a form so in that way according to you i can check when something is changed and by who.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Security on access (access 200 and up)

    You need to prevent people accessing the tables or queries directly, so you must lock up the application so they only see forms and reports. The Startup menu option under tools is the best place to start, but you also want to disable the bypass on startup using the SHIFT key. Then you need to provide some sort of back door so you as the designer can get in and do things when you need to. And finally you need to activate security - see our tutorial and the links - and then put code in your forms to update the person who last changed a record, and when. You can actually create an archive function if you want to track at the field level, but it's a great deal of work, so we choose to use SQL Server in that case. And in most cases simply knowing who last changed a record is sufficient to track down people related problems.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Security on access (access 200 and up)

    What I do is add four field to the record. Add User, Add Date-Time, Maint User, Maint-time. when the record is added the id of the user is placed in both the Add User and Maint user field with the same date/time. When a change is made I append the current record in the table to a log file in the 'Before Update' event. to keep a log file of all changes. (The log will now contain the record as it was added and the change is in the Master table. I update the Maint User and Maint Date-Time of the record just changed. After a period of time I off load some of the log file into an archive database. Hope this helps.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

Posting Permissions

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