How To Manage User Authorization and User Access in Access 2013 🎓
Вставка
- Опубліковано 8 січ 2025
- In this Access 2013 tutorial video, Steve Bishop explains how to manage the access that users have to your database application, also known as user authorization. This will help you limit which sections of your application that different users will be able to get to.
Click here for the Work Files:
github.com/Xip...
----------
Become a member today for only $5 a month!
/ @programmingmadeez
When you join, here’s what you’ll get:
Your name in the credits
A form to ask me for help on your project
10% off in the Teespring store (discount code)
----------
Looking for DAILY content? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things.
Coffee With Steve: / @coffeewithsteve
Steve, this video tells it all. I have been all over the internet trying to find someone to follow that can direct me the way that Relational Database Management Systems truly manage data. I have still some issues with Normalization and Relationships holding me back, but when i go back to videos that you produce like this one as an example, I am truly leaning this way again. This video seems to reduce the opportunity of outsider hacking substantially. I am pretty darn sure that there is not to too much more that you can do, to keep your users on a solid path in teaching them, that at that time to follow path that has been set out for them, and over time with some simple proof of service, they also can find themselves moving up the ladder and taking on more responsible roles over time. But the framework is in this video to keep your users within the scope of the roles set out and over time permissions or roles do change based on their history. So well done Steve, you are truly a master of disaster, prevention that is . . .
THIS GUY IS THE MOST POWERFUL TEACHER OF PROGRAMMING, I HAVE NEVER FAILED TO UNDERSTAND HIM.
Thank you Steve. I've worked in Access several years, but this is the first time I've had to use VBA for my databases. Your videos have been very helpful.
This video is really help and I am still enjoying them. God bless your effort for helping us with your knowledge. If I may ask, can you teach us or any ideal on "how to generate or create a licenses key/product key/serial number with an expiry date like (Non-Expiry, 6 months, 1 Year....etc) for your database. Maybe Offline or Online. I hope to hear from you.
Thank you once again for your time. Love you.
Very helpful Steve, I've learned a lot from you and i'm still learning you are my favorite VBA tutor ever. please keep it up and thanks a lot :)
+MRGhass Thank you so much for the kind words. I am glad you are enjoying the videos.
Looking for DAILY content? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things.
Coffee With Steve: ua-cam.com/channels/eXAUvo5xxDY_b-lSknPC1A.html
I saw just now your tutorial and I would thank you for your clarity, skill and competence in explain all the topics.
Great job. Three ways to check password authentication very intuitive and advanced.
Thanks.
It would have taken me three lifetimes to do it. THANKYOU! GOD BLESS!
Good job Steve. I appreciate your willingness to share your talent. God bless!
It has been a great experience watching your videos. Am glad that I met someone of such wisdom that I am hungry for now. Thank you so much for been an agent of change in my life and future career. CONGRATS Steve
Such an amazing presentation, not to mention how helpful this is for the project I am working on, I had to sign up for a membership. Thank you so much.
Nice to have you back! I look forward to your insightful videos.
atlantakick Thank you atlantakick. I'm glad I could get something up for you guys. It has been a while.
thank you Steve for your tutoring videos. I really enjoy learning and putting your teachings to practice.. very, very helpful..
thanks again and keep up..
Thank you Steve, your tutoring is absolutely the best
Thanks.. Steve. I always think you are the best for the Access programming teaching!!!!!
Thank you very much for your tutorial Steve, I'm currently attempting to build a training database for my department and your videos are really helpful.
Big fan of your series and instructional style. Thank you for posting.
YOU ARE THE BEST... very helpful Steve
Thanks a lot Steve, you are great at sharing knowledge!
Great video. Very useful and easy to follow. Great job!! Salute!
You are awesome dude ...
I watched your all videos earlier ..
And this one is very helpful..
Thank You Steve ..
Thank you, Steve _ Great video. Very useful and easy to follow.
Steve, you are a great teacher.
Excellent content. Beautifully explained.
Thank you Steve. Great Video again.
I have found your videos to be exactly what I need at the time I need them. Thank you so much for what you do. I had lots of fun restricting users from forms, but would like some help (or point me to the video) to restrict form fields for those that should have read only access. I created that as an option in my UserType table (you might have called it employee type). When the form loads, would love for only those with read/write access (or higher) to be able to modify fields (change table values). THANKS AGAIN
There are several ways to address this. I've seen people use a field with a numeric value to hold the access level. 0 is no access, 1 is read access, and 2 is read/write access. Then just check the value for the user and the section in a case statement to determine what the control appears like. Hidden for 0, visible but locked for 1, and visible unlocked for 2.
Excellent Video, followed step by step and all worked as should, however I did find that if the form I have put the ‘On Load’ code on, is a subform. I first get the “You do not have assess” message box as I should but when I click OK, it opens anyway. Any suggestions on how to prevent this?
Hi thanks for the video very helpful. You have another video explaining how to control what data (not form) can a person see and modify. For example sales representative can only see employees from sales department
Thank you Steve, it is very helpful and I have learned a lot for you
Good! I am so glad to hear it.
@Tyrielle Cole, that code has to do with the login process, not the logging of activity.
Programming
Awesome help! Im only running into one problem if the form is set to MAX on open. The form opens, then the popup tells them they dont have access
very need! can we so something the same with tables, queries and macros? i really admire the efforts to make those clear session on the topic. i pickup many new technics. keep going. cheers.
Thank you, learned a lot. Like your mode and pace.
very nice tricks. very useful video.
steve thanks a lot. loads of love to you
VErry good job! THX its helpfully!
Great video, very helpful, thank you Steve
Best regards
janez novak Glad you enjoyed it janez!
Great video, thank you!
I am looking for the coding needed to disable the Close Access button on my Access 2013 database. I have watched several of your tutorials and I love how simple you make learning the VBA coding. Could you possibly help me figure this out? I would really appreciate it.
Fantastic information and thank you for sharing.
You are very welcome!
Thanks Steve , you are Guinness :) , this is very helpful
+Amr Salah I'm not sure if being an Irish Lager is a compliment or not but I'll take it!
good one Steve it will helpfull
Thanks. I did not know about the TempVars collection. I can see myself using it in the future.
Hello Steve, Thank you so much, you are wonderful and helped so much. I do have one issue, on the Onload event, I have another piece of code that is filtering the form by Division … how do I add this other piece of code (user access) so they both worked. I did try Else but it did not work. I appreciate any help. Lisa
Great! Marvellous tutorial!
+Carol Taylor The compare binary will work fine. Unfortunately there's not much you can do about the warning message.
Thank you for video tutorials you posted. I found they are very informative and helped me to write vba code.
If I want to restrict user to only access subset of all records( e.g a user has access to one or multiple departments), can I replace object form as field name using this approach ? Thank you
You need a query that filters the records based on the department matching the Tempvar EmployeeType. (Assuming dept. and EmployeeType are the same)
Thanks a lot Steve, big fan of ur videos
Q. Can we link another database with the main database and when the form is submitted save database in another database ( not updating the linked access)
Thank you - your videos are very helpful.
Is there a way you can also control user access to tables and design view using this approach?
No. You should be making tables and design views inaccessible by disabling those features. If you watch my Programming In Access 2013 series, you will see how to do that in the later videos.
Fantastic, thank you bro.
very nice thankyou, useful info
It's very good
Please tell me it will be work with multiple user at same time?
Yes, it will. - Recommendation: Every user should have his own copy of the frontend file on his local computer.
you are smart! It's great. Thank you.
Very informative and helpful. How can I go to the link to get your source code. Thanks
Very Useful !, thanks
Will tempvars work like sessions variable in web environments?? So that moultiple users login at the same time with out mess up the values stored ??
hi steve thanks for the great video. I have tried your tutorial regarding creating a login screen and it did go well however when I tried to add this new code for managing user in my login form starting from tempvar it seems that other user except for the one i declared in "If rs!EmployeeType_ID = 1 Then" is not going through my log in page and also there is no error appearing on it. hope for your advice. thank you.
great as always
thanks a lot
Fantastic series, Steve. Much appreciated. Could this approach be used to Enable and /or disable multiple controls on a form? I currently have a form that different usertypes can access, but I only want 3 user types to be able to make changes to 3 of the controls on the form.
Yes. You can just set the controls enable property equal to the result of the check of the user access.
Hi Steve, your video is very helpful. I do have a question. While creating your tbl9EmployeeAccess, do you follow the basic database normalisation? I am asking because I would like to apply this procedure to many of my form and for each of them I will have to repeat 3 times the record and change only the HasAccess. Can we do it the same and follow the database normalisation? Thank you in advance!
Hello Steve
I wanted to know how to creat a bulk or group login
For example we have several sales groups or different companies are using the same app with multiple users at different security levels but all of them as a group can only see records associated with their company or group
I hope I was clear enough !
And thank you again I learned a lot from your videos
Ok you have used the codes to code the login form how about if someone who is familiar with those codes can they use the macros to do those codes made for the access to on the longing form instead of him/her typing in codes, please your advice is much needed.
hi steve, there is a db example? tks
hi i need help. i dont have globals under the module. what can i do?
Great video Steve! I am having trouble with my administration login section. When I log in as admin it doesn't really do anything different than the other profiles that I created. I might not fully understand it but I thought by turning the Bypass Key on that it is basically like opening the database while holding shift. Is this accurate? What exactly is turning the Bypass Key on do?
Thank you !!! You're awesome !
This was a very helpful lesson. I'm wondering if the same user access functionality can be applied to a tabbed form, where all users could see the first tab (page), but only certain users can see the other tabs (pages). Thanks!
Yes. Create a function that returns a boolean. Specify the employee type that should have access in an array. Loop over the arrays of employee type, then return true or false for each page per employee type, A Select Case per employee type would be needed, then you specify the return value for each page. You would need global variables to hold the page boolean properties returned by the function (you can use the tempvars ("page Name") to hold the function return value. On the OnLoad event of the Main Form:
Page.visible = tempvars("pagename") and you should be good.
What is the user name and password for the front end database? I want to access your code for the login form.
I wrote code to integrate my user table to an LDAP query function against our AD. This checks who they are logged in as and then only gets them what they have rights to see. No additional login prompt or password storage in an unsecure database. Great video though.
Dear Steve , do you have tutorial for user session management? example there are 10 user with different privileges , there are logout button to destroy the session (or there are other efficient technique to attempt this). Each user can not view other user input list (each user can only view their own created records). All in ms access matter.
Great lesson! Question concerning the TempVars, I was able to reference a record set in another form but is there a way to store more than 1 record set? I'm trying to reference a few record sets within the same username. Thanks again!
+Tim Chavez Sure, just create another recordset variable and assign it the value of the other data you want stored.
Thanks for the reply! Would you be able to provide an example code. I tried creating another TempVars recordset and it stopped my login code username and password. Thanks!
You should not be assigning tempvars recordsets. There isn't any need to anyway. You can assign a specific value to a TempVars item, but you won't want to do the entire recordset.
Thanks Steve!
Hello! Love your tutorials!
I have come across a few situations which will require some modifications to your Login Access form. From this login form is it possible for you to explain how to change the Login form to take you to a different form than other users depending on the login Username? I would like some users to open the database to an entirely different menu. I would love to see a tutorial on this if you can make the time for it. Thank you for all that you do for us!
If "AccessLevel" = 3 Then docmd.openform?
Logan Carter excellent thank you! I will give that a try tomorrow. Thanks again for the follow up :)
Very helpful !
Hi Steve this video is very helpful for me, is it possible to create user's environment form page where they can usually change their own password or username?
+Frizzell Abas yes, but the method of doing so is too big for a conversation in comments.
Oic. looking forwarrd if you are making tutorial vids.
great tutorial!!!
+Monzili “Germain” Mozagba Thank you!
Good Day Steve.
I recently came across your tutorials, which I have found very useful, and although I have a fair knowledge of Access i have not done a lot on Modules. I have just finished using your Login tutorial and everything works fine. I am now moving on to your How To Manage User Access tutorial.
Checking what I need in the Db I am using when I open the Modules in the Navigation pane I cannot see the module for the Login. If however I look at the Login Form and open the properties for the UserName and Password there they are.
Is this correct or have I done something wrong when creating the Module for the Login. Looking forward to your help ( with maybe many more questions to follow) I appreciate your time.
Regards
Mike
Question: It seems that TempVars will only be recognized until you close the access database. once you reopen it, the script will not work. what would I be able to use to have it there permanently?
You would need to store the value in a table. - Doesn't make too much sense for something related to the *current* user of your application.
Hi , Thank you for your videos, it has proved to be very, helpful, I have a question
when i use this code
UserAccess = Nz(DLookup("Has_access", "tbl1_Employee_Access", "EmployeeType_ID=" & TempVars("EmployeeType") & " AND FormName='" & FormName & "'"), False)
I get a runtime error 2471, Any suggestion what I may be doing wrong?
Hi, I wonder if anyone can help me. I think I missed a step. at 17.48, where You are talking about "TempVars" and using "EmployeeType_ID" I didn't see any of this (And much more of the code on this screen) in your "Live coding build a login screen".
Can anybody help?
Hi Steve. Can all of this be done with for a command button that is used to fill a form text box? Example: I've created a UserAccess table with columns for; SecurityNo, ButtonName and HasAccess. I want to use the information on that table with the user login, to allow or deny the user to click the button to fill in the text box above with the Windows UserName. If not...message box "You do not have access!" will appear. Simple... For you... Access 2019, btw.
HELP ME, PLEASE!!!
Hello, we are 5 mutli-users on Access working on a front end database that connects to a back in database. Our company pushed out an auto update of MS Office 2013, and all of a sudden only 1 user can get in at a time; normally all users can be in working at the same time with no "Locking" in records. The error we get is that the '...backend accdb file is already in use'. Again, the program works fine for 1 user but nobody else can connect to the backend simultaneously. How do we fix this? Thank you.
Hello Steve,
i downloaded the project files from GitHub, when i try to open the FE file it gives me the following error,
Run-time error '3244:
'C: the path is not valid
not a valid path. Make sure that the path name is spelled correctly and
hat you are connected to the server on which the file resides.
would you please help
Hi sir , im form bangladesh , thanks for excellent video.
But i want user read,right,view, delete,add permission how to use please help me.
okay, one other questions. Due to having a lot of different forms that different groups will need to have access to, i'm trying to see if there's a way to create a short cut, through creating two separate "launch menus" that load after the user logs in, utilizing the employeetype_id thought. Admins with employee type 1 would go to "frm_launch" and users with limited access (employee Id =2) to pull reports and view read only tables would go to "frm_smllaunch". The code i was trying to use would go at the end of the vba for the login form, after adding and hiding employee type onto the form. I've run it, and regardless of what person i put in, all are going to frm_launch, even if their employee type is 2. Here's what i've done- is this anywhere along the route to success?
Thanks!
If rs!Password = Nz(Me.txtpassword, "") Then
txtempl = employeetype_ID
End If
If employeetype_ID = 1 Then
DoCmd.OpenForm "frm_launch"
DoCmd.Close acForm, Me.Name
ElseIf employeetype_ID = 2 Then
DoCmd.OpenForm "frm_smlaunch"
DoCmd.Close acForm, Me.Name
End If
THANKS A MILLION!!!
Hi Steve, I'm learning mostly from your tutorials and I comparing to others in UA-cam and you are the best! I want to be a member in your channel. Right now I'm working on this log Screen for my project Auto-email reminder in our office, kindly consider me because along the way, I do have some questions/issues which you might help me. Thank you so much. By the way where I can download this code?
Since this is not setting permissions at the table level, presumably users will have free reign to do as they see fit once they do open a form meaning edit, delete, add unless of course the form definitions prevent such. But what if we want some users to have the ability to edit and others just read? Wondered if you have addressed that anywhere. I am thinking expanding on the HasAccess to include other options such as AllowEdits, Allow Deletions, AllowAdditions. Obviously that complicates things especially if the user should have permission to do multiple options. I think I know how I would address it but seems ugly and perhaps you have a good idea that you have already covered.
+ShoppersCritiqueIntl You can make allow edits, and allow additions properties false for the entire form based on the value stored in the table for that type of user.
Thanks steve, for your video, I am trying to code a button, when someone click the button, I want them to enter a password, if its a match, then i want the form to open. I like simple. sincerely your, Rheal
Hello, sorry for asking this dumb question here, may I know how to open the work file given? I keep getting [Run-time error 3044:
'c\user
inger\desktop\database\service inc_be.accdb' is not a valid path. Make sure the path name is spelled correctly and that you are connected to the server on which the file resides] when trying to login from the fe file. Is there anything that I missed?
Brilliant info
how does this work when you have multiple people logging into the same database? wouldn't that effect Tempvalues? i have one file location for a department or should i make links to new data bases that these members download to their desktop.
I tried this but for some reason it is not blocking access to restricted forms.
In this section of code:
DLookup("HasAccess", "tbl9EmployeeAccess", "EmployeeType_ID=" & TempVars("EmployeeType") & " AND FormName='" & FormName & "'")
If the "EmployeeType_ID" referencing the tbl9EmployeeAccess table or tbl2EmployeeTypes table?
Thanks.
Hi Steve Nice, What if I wanted to restrict only a few controls on a form. For example some user may have view access, while others may have add, delete and ofcourse view access. How can I achieve this? Thank you in advance
Instead of the visible property, you can use the enabled property to disable a control, but still have it's contents visible.
Steve great video...keep them coming!
Tried to the using the GlobalModule in managing user access relating to forms and I'm coming up with a compile error: Expected: list separator or )
I've watch the video several times and I believe I have it correct, but still coming up as an error?
Public Function UserAccess(FormName As String) As Boolean
UserAccess = Nz(DLookup("HasAccess", "tbl9EmployeeAccess", "EmployeeType_ID=" & TempVars("EmployeeType") & " AND FormName='" & FormName & "'"), False = False Then
End Function
John Vage you're missing a ) after your first False and before the equal sign.
Programming Thanks, I knew it was something easy/glossing over!
Hey Steve, the video is a few years old but i hope you will answer my question nevertheless. If I split my database into front- and backend, the users see all connected tables in their frontend as well. Theoretically they can see so any informations from the tables, also the passwords of the other users. How can I prevent this? The other point I want to know is, how can I prevent that users simply write informations directly into the tables even if the equal formular is locked for them? How can I prevent, that the users can see the content of the connected tables? Thanks a lot! :)
Very useful. However, the confusing part is that it seems you are bypassing Access work group security. Shouldn't we set permissions using the work group (group and user level security)? For example, when you use Current user() it returns Admin not id used to log in. Am I missing something?
Thanks
Hi! I am really new to access, but I have made a database which is going to be used by ten people. So I would like to manage their access not only to the forms, but also to the tables. I have seen your video about splitting the database into back end and front end, but my database is not that big and I don't want to split it. So I would like to ask you is there another way to limit the access of the regular users, so they wouldn't be able to see some of the tables. Thank you. :)
Hi Maria
I would strongly suggest that you split the database. If you are trying to control access to portions of the database it really is the way to go. Also with that many users you will shortly have a situation where two users want access to the database at the same time. With a split database arrangement across a network this is easily accomplished.
Hi Steve thanks a lot, but how can i open the Front end database ? database ask me to log in, but i don't have any login id and password, please help me