User authentication with MS ACCESS 2007

Posted on 2010-03-29
Medium Priority
Last Modified: 2013-11-29
I need to implement user authentication in ms access 2007 application. I need to create user roles and permissions to wirte some records , but not only to read from other records. Also, I would like to track which user modified  record.
I have a multi user ms access application with back end on SQL server 2008, but i need to implement security on ms access not in sql server. Any suggestions or may be vba code samples?
Question by:maximyshka
  • 4
  • 3
LVL 77

Expert Comment

ID: 28979157
Are you using an adp, and accdb or mdb file as your front-end?

Having asked that, if your backend is a secured sql server database I cannot understand what you feel you can gain by securing the front-end?
LVL 85
ID: 28979553
Agree with peter: Why would you go to this trouble when you have a robust security system in place with SQL Server?

I can certainly understand wishing to build a navigation system in 07 (where UserA can open FormB but not FormC, for example). If that's the case, then you might check out LASsie by Peter's Software (www.peterssoftware.com), which shows how to do this.

You can also build this yourself by adding tables to track Users, Groups, User + Group Associations and Object + Group permissions. It's not overly complex, but will take some work to implement. Once you've defined the permissions, then just build a group of VBA functions that provide you an easy interface to determine permissions, and you should be okay.

Author Comment

ID: 28979846
i plan to use accdb (access 2007). I need to provide user based functions on ms access forms.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 85
ID: 28980286
Access forms provide several different methods to allow/disallow basic user interaction with your data:


As well as the standard .Enabled and .Locked properties of controls and such. So you could do as i suggest, and build your tables to house your various items, and then build functions to interact with those tables to determine which user can interact with which object.

If you build the tables and post them back here, we can critique them and let you know what changes need to be made.

Author Comment

ID: 28980791
so i create table users, whic will contain userName, Password, permision level
Once user loged in , i can check user password and permision level and open form . How can i track loged in user through application, through other forms?
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 28981329
Most people include a hidden form (or just hide the login form after authenticating) and simply refer back to the User textbox. Or you can use global variables (a bad idea in my opinion), or class modules - each has their own merits, and each has their own detriments. For the most part, the hidden form is the easiest to deal with.

As far as tracking changes, once you've authenticated the user, you'd just use the various form Events to track those changes: AfterUpdate, AfterInsert, AfterDelConfirm etc etc. You'd just flag those records as needed with the username, date, etc and that should do it. Of course, if you're actually allowing users to remove (i.e. Delete) record permanently, then you've got a lot more work to do.

Allen Browne has a great example of audit tracking here: http://allenbrowne.com/AppAudit.html


Author Comment

ID: 28981449
I guess I can create module with global variable Global GBL_Username As String , and record username . then when i open each form i will have to run this variable against permission level .

Author Comment

ID: 28981622
why do you think that global variable a bad idea?

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

589 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