User authentication with MS ACCESS 2007

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 (, 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.
maximyshkaAuthor Commented:
i plan to use accdb (access 2007). I need to provide user based functions on ms access forms.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
maximyshkaAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maximyshkaAuthor Commented:
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 .
maximyshkaAuthor Commented:
why do you think that global variable a bad idea?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.