Proper Security for your Microsoft Access Databases
This seminar will teach you how to properly secure your Microsoft Access databases. But that's not all. You will also learn how to build proper security into the workflow of your database so that you can control every aspect of a user's experience, and monitor their work as well in a database log.
You will learn how to properly secure your Access databases to control who can log in, who can edit data, and who can make changes to the database itself. But that's only the beginning. You will learn how to build a database where you can control every step in the "work flow" process of data entry, and create a system log so you can see who is doing what.
Main Seminar Goals
You will begin by creating a user information table (with username, password, and other data) and a secure logon form. This will allow us to control who can log on to the database.
Next we'll set up a Group form so we can assign users to different groups, like SalesRep, ServiceTech, Manager, Admin, etc.
In the next lesson, we'll define a "work flow" for our database. This will control how information is entered by our users. For example, our database will be for service work orders. Our work flow will consist of a sales rep entering a work order, a service technician performing the work, a manager reviewing the details, the sales rep follows up with the customer, and then the manager closes the work order.
You will be able to control which users will be able to enter data into which fields. For example, only service technicians can enter data into the "service performed" field, but not the "service requested" field, which is the job of the sales rep.
We will control each step of the work flow process with buttons that signify that a user is done with his work, and the work order is ready for the next person. Here you can see a "service call completed" button that will then send this work order to the manager for review. This work order will then be locked out so the service tech can't edit it any further.
You will learn how to send Email notices from inside the database when needed. This is great if you have employees that don't check the database all the time, so as to say, "your assistance is required."
The database will log every step in the work flow procedure. You will be able to see who did what, and when.
We will also create a system log table, so we can log everything that a user does: what time he logs on, what data he adds, what records he changes, etc. You name it, we can log it.
You will learn how to build your own internal database security. For example, do you want sales reps to be able to edit other sales reps' customers? Do you want service techs to be able to see customer data if they're not working on that customer's work order? Who can edit, delete, or add new customer records? What information should be required for new customers? Some of these are things you just can't do with standard Access User-Level Security.
You will be able to hide or show buttons and forms based on a user's security level. For example, the menu that a system administrator might see...
...will be completely different from what a simple service technician sees. This will help to control who can do what in your database.
We will spend a lot of time learning how to lock down your database and secure it from intruders. We will split the database into front-end and back-end files, encrypt the database with a password, turn off the Navigation Pane (database window) to keep people from poking around in your objects, set a Startup Form, and disable the Bypass Key so people can't go to the Navigation Pane directly when the database starts up.
You will learn how to dynamically link to tables in your back-end database using VBA code to overcome some security problems.
We will encrypt the front-end database into an ACCDE file (MDE file for older versions of Access). This will effectively secure the form, report, and module design capabilities and prevent people from viewing your database in design mode.
For our Access 2003 users (yes, there are still a bunch of them out there), we will spend some time going over the User Level Security Wizard. This wizard will secure an Access 2003 database and provide "OK" security if all you want is to keep Joe from Accounting from poking around in form design, and seeing data he's not supposed to. It's not hack-proof, however, but it does provide "quick and dirty" security in a pinch. We'll set up users, passwords, groups, and permissions.
This seminar is perfect for anyone who wants to learn how to secure an Access database, prevent someone else from accessing parts of the database they shouldn't, assign permissions so you can control who does what, and create a work flow strategy in your database.
This seminar is long (over seven hours) but it's broken up into easily managed lessons of about 10 minutes each. You can sit down, watch a lesson, review the material, test the code out yourself, and experiment. Do a little bit each day. It's long, but it's comprehensive - you won't miss a single step as I've recorded everything from start to finish.
This is a Developer-Level Seminar. There will be a lot of VBA. It is strongly recommended that you have completed my entire Access Beginner and Expert series. My Developer 1 class is highly recommended so you understand the basics of programming in VBA. If not, at least watch my free Intro to VBA video.
I am using Access 2007 in this seminar, however the lessons are perfectly valid for all versions of Access from 2003 and later. It's currently 2022 and I just recently verified that everything in this seminar still works with Access 2019 and Office 365.
Enroll now so that you can watch these lessons, learn with us, post questions, and more.
Please feel free to post your questions or comments below. Thanks.
microsoft access security, user level security, group security, administration, work flow process, audit log, audit trail, user activity log, system log, show hide buttons, lock down, encrypt, accde, user level security wizard
You may want to read these articles from the 599CD News: