User Level Security 2
By Richard Rost
3 years ago
User Level Security in Microsoft Access, Part 2
In this Microsoft Access tutorial I'm going to teach you how to control which forms and reports specific users can open in your database. This is a follow-up User Level Security Part 1 video.
Disclaimer
This tutorial will show you security that is "good enough" to keep most users out of your database. However an experienced Access developer will be able to bypass this logon routine. To learn the best possible way to secure your database I recommend my Microsoft Access Security Seminar.
Members
There is no extended cut, but here is the database file:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Next Video
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, how to control what users have access to, Access Control Overview, Control user access to environments
Intro In this video, we continue working with user level security in Microsoft Access by learning how to control which forms and reports specific users can open based on their logon credentials. I will show you two methods to manage access: adding code to buttons that open forms and reports, and placing security checks directly in form events using VBA. We will also cover how to handle bypassing the logon form as an admin and ways to avoid common errors when restricting access. This is a developer-level tutorial and builds on what was covered in Part 1. This is part 2.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. This is User Level Security Part 2, a follow-up to, guess what, User Level Security Part 1, where I showed you how to create user level security with usernames and passwords and logons and all that good stuff.
Now that we have that all set up, in this video, I am going to teach you how to control which forms and reports specific users can open in your database.
This is of course a developer level video. That means you need some VBA, and of course, it goes without saying that you really need to watch Part 1 first. So if you have not, go do that. You will find the link down below.
Before we get started, there is one little thing that I want to mention. Let me open up the database. When you open up the database, you get your little logon form right here, and that is all great and fine and dandy.
But you, the admin, if you are doing some design work on your master copy, your ACCDB copy, you might want to bypass the logon form because it is a little bit annoying to run into it every single time. How do we bypass the logon form? Well, we bypass the startup by holding down the Shift key when we open up our database, and there we go. It bypasses all the startup stuff.
Now, can you prevent your users from doing that? Yes, you can. It is pretty advanced though, and that is one of the techniques that I teach in my security seminar, which we will talk more about at the end of the video. But your average user is not going to know that trick unless they know Access.
The problem is now if I open up my main menu, I get this invalid value. So why is that? Let's debug it. MainMenuLabel.Caption equals tempvars username. I never assigned a tempvars username because the logon form never ran.
There are a couple of different ways you can fix that. You can assign a default here, or you can just exit the database and force the user to log on. But I will just assume if at this point, the database is running and they do not have a username, it is me as the admin logging on. I can just say if IsNull tempvars username, then we will just assign it some value. tempvars username equals nobody, or John Luke, or whatever you want to put in there.
At least now I will be logged on as nobody. If you close the database now and open it back up again, bypass it this time, hold the Shift key down. Now at least I am logged on as nobody and I do not get an error message.
That will be just for you. Your users, you want to force them to log on. You, as the developer, might not want to always log on to get in and do something quick.
At this point, we are going to assume our users are logged on, which means they have got an account in this table. So how do we keep users from going in certain places? Some things, like your customer list or customer forms, you might want to let anybody in there. Maybe you have a specific, special accounting form that only Joe and Bill from accounting are going to be able to go into.
There are two ways you can control who can do what. You can either control it in the buttons that open those forms or reports, or you can put code in the object itself, like in the form's OnOpen event. I will show you both ways.
Let's pretend it is the customer list you want to control. Right-click, build event. I am going to close the project explorer. Right here is the command that opens up the customer list. All you have to do right here is just check to see who is logged on.
If tempvars username equals Joe or tempvars username equals Bill, or whoever else is allowed, then do the thing. Else, message box: Access denied. It is that simple, check to see who is logged on. If it is not Joe or Bill, save it, close this, reopen it, and try to go in. Access denied. I am not Joe or Bill. I am nobody. You could make a master user, admin, or whatever you want to do.
Again, in my security seminar, I actually show you how to set up groups. If you have lots of employees, 20 or 30 employees, you might want to put people into groups. So instead of saying if username is Joe, you could say if group is not users or accounting or whatever, then you could do it that way. It is a little more complicated, but it is just another table and putting users into groups with permissions. It is not super hard.
Now let us see the other method. Let us do it from inside the form itself. I will get rid of all this stuff. We are going to open up the customer list and we are going to control what happens in the customer list form itself.
Open it up, right-click, design view. Go to the OnOpen event. The differences are OnOpen can be canceled, whereas OnLoad cannot. In here, it is basically the same thing, you just have to change the logic a little bit.
You are going to say if tempvars username is not Joe and tempvars username is not Bill, then Cancel = True, then message box: Access denied. End if.
Yes, I know Cancel is an integer, it is expecting a number, but True is negative one. All this really cares about is zero or not zero, and Cancel = True sounds a lot better than Cancel = 1. I just use True and False and it always works.
Now, this is going to throw one more little problem. I will show you how to deal with that in a second. Save it, close that, close it, open it. Access denied because I am nobody, not Bill or Joe.
One more minor thing you have to do. Hit OK. It is going to say: OpenForm was canceled. Debugged. I got canceled because we canceled it in the form. So just put an On Error Resume Next on top of that one, and yes, that is okay. That is perfectly fine. Do not listen to people that say you should never use On Error Resume Next. In a simple situation like that, there is nothing wrong with it.
I was reading an article by someone written a couple of years ago, and they said, 'You should never use On Error Resume Next.' It is not awful programming. It is fine. If you have got a simple little one-line event like that and you want to just cancel it if there is an error, do not listen to those people.
I have often found that people who are more like academics and have just worked with Access in the classroom have not built databases in the real world. They have some weird concepts of what is okay and what is not. I have been building databases for 30 years. I know what I am talking about. Now I sound like get off my lawn.
That is pretty much it. That is the two ways you can control who can do what. You can put it in the button. I personally prefer the button approach myself because I have buttons on all my menu forms that control who can do what. It is just a matter of putting the control in the buttons. You can create functions for accounting people or groups or something like that. I cover that in my security seminar.
Once again, I will give the disclaimer that this tutorial shows you security that is good enough to keep most users out of your database. However, an experienced Access developer will be able to get around this stuff. That is why I recommend my Access Security Seminar, which will teach you how to lock down your database as well as possible. That is what I show in the security seminar.
No software application is 100 percent bulletproof, and Access is, at best, 99.9 percent bulletproof. But I show you how to make it as bulletproof as it can possibly be. This plus SQL Server is a really good, secure solution.
So that is it for Part 2. In Part 3, I am going to show you how to have it so that users can only view and edit records they themselves created. For example, each user can have their own list of customers in the customer table, and they can only see and edit their own customers. That is coming up in Part 3.
That is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.Quiz Q1. What is the primary focus of User Level Security Part 2 in this tutorial?
A. Creating user logon forms
B. Controlling which forms and reports users can access
C. Setting up SQL Server integration
D. Designing database relationships
Q2. Why might a developer want to bypass the logon form during database design?
A. To test user accounts
B. To speed up access during development and avoid the logon every time
C. To allow all users unrestricted access
D. To troubleshoot SQL Server connectivity
Q3. How can the database startup process be bypassed as described in the video?
A. Holding down the Ctrl key while opening the database
B. Removing the logon form from the startup options
C. Holding down the Shift key when opening the database
D. Renaming the ACCDB file
Q4. What issue occurs when the logon form is bypassed and no username is assigned?
A. The database automatically closes
B. The main menu shows an invalid value error
C. All users are given admin privileges
D. User data is deleted
Q5. What is one way to handle the absence of a username when the logon form is bypassed?
A. Notify the user and close the database
B. Assign a default username such as "nobody"
C. Generate a random username
D. Prevent the main menu from opening
Q6. When restricting access to a form based on username, where can the control logic be placed?
A. Only in the table design
B. In the buttons that open forms or reports, or in the form's OnOpen event
C. Only in the database properties
D. In the database macro settings only
Q7. What is checked to determine if a user should have access to a form?
A. The database file extension
B. The name of the user's computer
C. The value stored in tempvars username
D. The form title
Q8. In the example provided, what happens if a user who is not "Joe" or "Bill" tries to access a restricted form?
A. The form opens blank
B. A message box appears saying "Access denied"
C. The user is logged out
D. The database closes
Q9. What advantage does placing security checks in the form's OnOpen event provide?
A. The form visually appears before being closed
B. The opening of the form can be cancelled before it loads
C. It allows any user to bypass security
D. It automatically encrypts the form
Q10. Why does using Cancel = True in the OnOpen event effectively stop unauthorized users from accessing the form?
A. It closes the entire database
B. It makes the form invisible
C. It cancels the opening of the form based on the condition
D. It logs the user in as admin
Q11. According to the video, which VBA statement can be added to handle the error that occurs when a canceled form fails to open?
A. On Error GoTo 0
B. Resume Next If Error
C. On Error Resume Next
D. End If Error
Q12. Why does the instructor say using "On Error Resume Next" is acceptable in this particular circumstance?
A. It is required by VBA
B. The error is harmless and cancelling is intentional
C. It keeps the database running faster
D. It hides all other database errors
Q13. What is the instructor's preferred method for securing forms and reports?
A. Database-level user rights settings
B. Using button logic to check permissions
C. Encrypting the ACCDB file
D. Removing all restricted forms
Q14. What additional feature does the instructor mention is covered in his security seminar for databases with many employees?
A. Using Active Directory integration
B. Creating permission groups and assigning users to them
C. Automatically generating passwords for all users
D. Disabling all macros
Q15. What disclaimer does the instructor make about the level of security shown in the tutorial?
A. It is unbreakable, even by professionals
B. It is enough to keep most users out, but experienced Access users can get around it
C. It is approved by Microsoft for enterprise use
D. It automatically updates with new threats
Q16. What upcoming topic is previewed for Part 3?
A. Users having different password requirements
B. Users can only view and edit records they themselves created
C. Two-factor authentication in Access
D. Migrating to a cloud-based solution
Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-B; 7-C; 8-B; 9-B; 10-C; 11-C; 12-B; 13-B; 14-B; 15-B; 16-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers User Level Security Part 2 in Microsoft Access. In my previous session, User Level Security Part 1, I explained how to set up usernames, passwords, and a logon form for your database. Now that the foundation is in place, I am going to focus on controlling which forms and reports specific users can access in your database.
This is targeted at those of you working at the developer level in Access. You will need some VBA skills for what I am about to explain. If you missed Part 1, it is vital to watch that first since it contains all the information needed to set up user authentication. The link is provided on my site.
Let me address a common issue before getting into permissions. When you are developing your database and working on your ACCDB master file, facing the logon form every time can be tedious. To bypass this logon form during development, you can simply hold down the Shift key while opening your database. This disables all the startup routines, including the logon screen. Most regular users will not know about this technique, but it can be prevented. I cover that advanced technique in detail in my security seminar.
Keep in mind, bypassing the logon means you are not initializing necessary environment variables in your database, like the username. If you try to open your main menu under these conditions, you might end up with an error or unexpected value because there is no username assigned. You can handle this by assigning a default username if none exists—something like "nobody" or any placeholder of your choice. That way, you avoid errors when logging in as the developer who is bypassing the logon. This is not for end users; they should always be made to log in through the normal process.
Assuming your users have logged in properly and have accounts in your user table, you may want to restrict access to certain forms or reports. Perhaps everyone is allowed into customer-related forms, but only specific people like Joe or Bill from Accounting should be able to access the accounting form.
There are two principal ways to control this kind of access. First, you can restrict access at the button level. That means adding logic to the VBA code behind the buttons that open your forms and reports. Check the current user's username using a simple test. If the username matches your permitted users, open the form or report. If not, display a message that access is denied.
For example, to control access to a customer list, build the event for the button that opens that form. In the code, check if tempvars username matches the approved list. If not, show a denial message. It is straightforward and keeps unauthorized users from accessing sensitive areas.
If you have a large team and want to handle permissions by group rather than by individual usernames, I cover that in much more detail in my security seminar. In that scenario, you would create groups, like "accounting," and assign permissions to the groups rather than individuals. This just means a little more work setting up tables and assigning users to groups, but it streamlines permissions management.
The second method is to control access within the form itself, using the form's OnOpen event. Here, you add code that runs as soon as the form is opening. Check the username, and if the current user is not authorized, set Cancel to True and display an "Access denied" message. This method is powerful because it cancels the form opening, regardless of how someone tries to open it.
This approach can lead to an error like "OpenForm was canceled," which you should handle with an On Error Resume Next statement. This is an acceptable use of On Error Resume Next, especially when dealing with simple event handlers like this. Do not get too caught up by critics who claim this is always bad practice; in the real world, it is often necessary and works just fine.
Between these two methods, I personally prefer handling access at the button level. Most of my menu forms use buttons to control navigation, so putting permission logic there is simple and effective. You can expand this to check for groups or create more advanced permission logic as needed. Again, all of that is explained in detail in my security seminar.
Keep in mind that the kind of user-level security shown in this tutorial is designed to keep most casual users out of restricted database areas. However, someone with advanced Access knowledge may still find ways around these techniques. For more robust protection, I always recommend my Access Security Seminar, where I teach you how to lock down your database as tightly as possible. No software is completely invulnerable, but you can make Access as secure as possible, especially when paired with SQL Server.
In the upcoming Part 3, I will show you how to restrict users so they can only view and edit records that they themselves have added. For example, each user will have access only to their own set of customers in the customer table.
You can find a complete video tutorial with step-by-step instructions on everything we have discussed today at my website at the link below. Live long and prosper, my friends.Topic List Bypassing the logon form during database startup
Preventing users from bypassing startup with the Shift key
Handling missing TempVars values after bypassing logon
Assigning default usernames when the logon form is skipped
Controlling access to forms with button event code
Displaying "Access Denied" messages for unauthorized users
Restricting form access using VBA in the form's OnOpen event
Using the Cancel property to block form opening
Error handling with On Error Resume Next in form events
Choosing between button-level and form-level security methods |
