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

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?

Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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.
Paolo SantiangeliConsulente InformaticoCommented:
There is no log of changes in an Access(Jet) database.
If you want this information you must program it yourself.  
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

scudrulesAuthor Commented:
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!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
scudrulesAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
scudrulesAuthor Commented:
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!
scudrulesAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.