Microsoft Access Security: How To Login Into Your Access Database
With the release of Microsoft Access 2007 introducing a new file format (ACCDB), the Microsoft Access security completely changed. In fact, it dropped the MDW workgroup security utility altogether in favour of trusted locations and/or using SharePoint.
However, I wanted to have the ability to control my own collection of objects within my Access database and not rely on either a third party tool or online service but instead keeping it all in one place having full control of my database.
You can still apply a basic and global database file password to your ACCDB files but everyone will need to use the same password and gain access to all areas of your database – not really recommended!
So, how can you login into your own custom built database with the later versions (2007, 2010 & 2013) and take full control or your own access rights?
Well, the simple answer is to build it yourself and mimic the Workgroup and Access Rights utility of old.
The advantage here of course is you have full control of what level of security you really need and eliminate all the unwanted other elements that the older MDW utility provided confusing most users and developers a like. The only downside of course is you need to build it!
Microsoft Access Security: How To Login Into Your Access Database – My Solution
There are three key features required here:
- Login via a valid password for the known user.
- Workgroups which a user is a member of.
- Access rights applied to each workgroup which users inherit
The simple workflow is a known user which is maintained by authorised administrators (and are in turn users too) to allow access to the database.
A user must belong to a workgroup as defined by administrators which they inherit the access rights to the associated forms and reports as either full edit or read only (where applicable).
Additional functionality can be included to:
- Users to change their own password.
- Administrators to enable or disable user accounts.
- Set default start-up forms for an individual user.
- Fully lock down the database to prevent unauthorised access.
I recently created my own Microsoft Access security tool that ended up with ten forms and six supporting tables to achieve all the above. With a mix of using the standard embedded macros and applying my own Access VBA code, I ended up with a generic system that could be plugged into any Access database.
The user starts the database and sees a prompt:
They enter a valid password (which I made case sensitive too) and if incorrect:
If authorised, then they enter their start-up form and off they go:
If a user is also an administrator, they can also enter the manager tool to control users, workgroups, and their access rights as well as set options:
Using Access VBA code, I was able to build the above and take full control using a combination of built-in functions, macros and wrapping my VBA code around it to enhance the rich tool set of Access.
I appreciate some readers here may not have the knowledge to code in Access but a small investment of time to go and learn and research the basic concepts will pay dividends.
Alternatively, I know some of you will go on an advanced training course or perhaps consider the best compromise that will teach you, provide the code and physically end up with a working solution by considering this tool I have built and want to share (for a modest fee!).
This is my extended and newer user login security tool which now includes workgroups, access rights and additional options that my clients have been asking for over a year now and felt the need to bring this to the attention of all my members here.
You can either build it following the 60+ page user guide or simply go and download the finished product and plug it in. In all cases, I will always answer all your questions (if you have any) with my free 30 day email support as part of the offer.
If you have been using Access for a while now, you will know unless you lock down the database before distributing it to end-users, you are leaving yourself open to vulnerabilities. Therefore, this user guide also provides tips on how to lock down the database within the housekeeping section provided.
In the meantime, I would love to hear from you if you have built a similar product for you own needs or your clients that you would like to share (in the comments box below).