MS Access - Store the log of the last updates along with user information

Posted on 2011-10-14
Last Modified: 2013-12-25
Hell experts,

Greetings! I have a two part question. Please let me know if it is OK to post in one shot or if it needs to be two separate questions.

Here is the question:

1. I have an interface written in Visual basic 6.0 with multiple fields. I am using MS Access database as a back end to retrieve and update data. I would like to have secure the access to the database via the interface. I have a table with user name and password (only two fields). How can achieve this from VB 6. I have looked into this help here >>  but I am having hard time understanding it. Also, please bear in mind that there will be multiple users opening the database via this windows application at the SAME time. So, it should not lock the database out to one user.

2. Assuming that the user is authenticated, he/she will make changes to the data table via the application. I would like to store a log of who the last user was that accessed the database and what changes and which table was updated. Is this possible in MS Access?

Question by:scudrules
    LVL 11

    Expert Comment

    LVL 77

    Expert Comment

    There is no log of changes in an Access(Jet) database.
    If you want this information you must program it yourself.  
    LVL 84

    Accepted Solution

    Also note that User Level security was removed in Access 2007, so be aware of this if you move forward with ULS.

    It seems, however, that you are "rolling your own", since you mention a table and such. You can certainly do this, and it will work as a navigation aid, but it won't provide you with any data security. In fact, Access ULS is hardly secure, and can easily be broken by many utilities.

    So if your goal is strong data security, you should consider a different data store like SQL Server Express. This is a robust database engine and has a very strong security mechanism.

    If your goal is simply user navigation, then I would submit that you'd be a lot better off "rolling your own" than dealing with Access User Level Security.

    The concept if fairly simple. You need to track Users, Objects (i.e. forms, reports, etc) and Permissions. In many cases it's advisable to track Groups rather than Users, since it is rare that a single person will have permissions unique to themselves, and will instead share those same permissions with several others.

    So you'd have something like this:

    GroupID (Autonumber, PK)

    UserID (AN, PK)



    <see below>

    From here, you would then determine how you'll store the different types of permissions. How you do this depends on the granularity you need for these objects. If you're really only concerned with allowing or disallowing users the ability to open a form and work with the data on that form, for example, then you might just add fields to the Permissions table to indicate this, for example:


    These would be boolean fields, and you can set them to True or False based on what level of permissions that specific Group would have with that specific Object.

    If you need much tighter control, and need to control whether a specific Group can access certain features of the form, then you'll have to consider other methods to store the Object+Group permissions. For example, if you need to allow one group of users to access TextboxA, B, C and F, and TabPage4, but not Combo1 or 3, or TabPage 6 or 7, then you'll need to add tables to manage this, or perhaps consider using some sort of bitwise logic. In my opinion, the table structure can become unweildly at times, but it's the better method to use, as it allows you more flexibility as you add new features to your interface.

    Author Comment

    psantiangeli, peter57r and LSMConsulting - Thanks so much for your answers. Although I more inclined to LSM's answer cause being a noob I "got" what he is saying.

    So LSM, I have followed your table structuring w.r.t the security and pretty much got what I wanted. Also, this application is not of a grave importance that it would make a big deal if the security was "breached" by other means. It is a way to streamline how the user enters "trivial" data and its maintenance. So that being said - going to the second part of the question -- Logging. And I understand there is no built-in logging mechanism in JET database. But I know I can achieve this programatically. My initial idea was to have two fields (Name and Date) in every table. So on the interface, the user is forced to enter his/her name just before the Update/Add/Delete is clicked. And once one of those buttons was clicked, the table will be updated with the name and current date information. In addition to that, there will be a "Changes" table, that will store all of the values that were passed to the backend along with the User and Timestamp info. I think this is a rudimentary way to achieve my goal. But do you think if there is any other way? Thanks for your help!
    LVL 84
    Sounds good to me, but I'm curious about the Changes table. What will you actually store there? Will you store the SQL you're passing to the backend database, for example?

    Author Comment

    Yes. I want to store the whole data that is being sent to the table as a blob. Do you think of any other efficient way?
    LVL 84
    Do you mean in a Memo field? I ask because some confuse Memo fields and BLOB/OLE fields. Assuming all your data is Text/Numeric, you should use a Memo field in Access.

    Author Comment

    LSM... thanks for your responses. I have built in a system based on your suggestions and it works as needed. Thanks for all your help! You guys rock!

    Author Closing Comment

    Although, this question received multiple answers and all of them equally good, the main reason that I picked this one as answer is because I "got" what he said and it laid out plan for me to build on.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now