How To Manage User Authorization and User Access in Access 2013 🎓

hello everyone and welcome to how to in Access 2013 my name is Steve Bishop and in today's video we're going to be talking about how to manage user access to the various sections of date so let's say for example you have regular users and only expose sections of the database users should have access to you don't want them to have full administrative privileges for example and then there are certain sections of the database maybe you want only administrators to have access to things like user accounts and you know settings within the database itself so that nobody can go in and screw with anything so you're going to want to limit the access the different types of people have access to in your database this video is going to outline one way of handling all that there are multiple different ways that you can set up manager user to manage user access this is just a good middle of the of the ground you know it's not too basic and it's not too complex it's pretty simple to manage now this is going to be a video that is an extension of the programming and access 2013 video series that I did so if you have not seen that video series I highly recommend that you do so but if you have not seen that video series don't worry you can still watch this video and probably pick up plenty of information that you can utilize in your own database that you're working on in your environment so either way you should be able to use this video I will also make the source code of this database available to you as a link in the description of the video so for free you can just go ahead and download the saw download the source code take a look at it open it up you know and compare what I've got to what you've got in your own system so there we go let's go ahead and hop out here and take a look at the database as it stands and I just kind of want to give everybody a lay of the land because again some of you are probably coming to this video not having seen that programming series so I just kind of want to show you guys where everything is first and foremost the database is actually split up into two separate files one is the front end data file that gets distributed amongst everybody in in this you know the amongst everybody that's going to be utilizing it and that front end is split to have queries forms reports and modules where all the VBA code is all that is going to be in the front end while these tables are actually linked from the front end to a back end file that has all the tables and all the data in it as one single file that doesn't get distributed to anybody it sits in a secure location on the network and really the only way anybody has access to this file is through the front end file that we give them okay so all of that being said let's talk about a little bit of some of the internal workings of this database that we've got set up so far first and foremost one of the things that you need is some sort of table that contains user login information so we've got user names passwords and who they are so this could be a user table we've called it table one employees but just basically a table that each record is each ending into the database and this is separate from Windows lock-ins okay this is not based upon the windows login setup it's strictly its own internal system of tracking users okay and then you of course need some sort of login form that the user is presented with when they log into the database or you know when they try to run the database they're getting prompted to put in a username a password and to log into the database using their credentials so hopefully you've got all of that set up if you have not again I have a beginner series that we go over all of those things on all of those concepts so if you don't already have a login screen with user information and a user table you probably want to go ahead and view that series now but let's go ahead and assume that you already have all of that so let's go to our table here where we've got our users and you'll see that I have a column here called employee type underscore ID this employee type underscore ID table is a foreign key field that points back to the table to employees types and so you can see that an employee with an employee type ID of three is actually an administrator and an employee with a type of two is an account manager and then we of course have the number one which is going to be a sales representative so that's basically how we are tracking our you know keeping track of what type of employee is logging into the database we can take a look and see that when I log in I am an administrator to the database so we want to relate this information this employee type ID to form or report that the user tries to open okay that's the relationship that we're creating here between the form that we're opening and what type of employee they are and in our specific example that I'm going to demonstrate we've got this form employees form which is a very sensitive form it has the actual employee information edge so first-name lastname passwords username wages etc so whatever sensitive information that might be contained on this and on this form should really only be available to somebody who is administering the database you know somebody who should be doing the user new user account creation and changing passwords and that sort of thing so we only want to have administrators be able to see that particular form so we're going to make sure that employee types of three are the only ones that are able to see this form employees form so since this is a relationship that we're creating between the employee type ID and the name of the form and we're using a relational database management system called access it service it should probably become somewhat obvious to you that we're going to utilize the facility of relational database management to create a relation between different objects that's kind of the point of the relational database systems so what we're going to do is we're going to create a new table and this table is going to contain the information about those relationships okay so the first column that we need to put in our table is the first part of the relationship which would be what is the employee type okay and so - employee type underscore ID and that is a number field okay and the other half of our relationship the name of the form that's trying to be open that would be form employees so our next column is going to be form name and that's going to be just a short text okay I'm going to make one other column here and that's going to be has access and essentially what I want to do with this has access field is I'm going to make it a yes or no field or a true or false a bullion field whatever you want to call it and access it's a yes or no field and this is actually going to be the result we want to return a yes or no as to whether or not that particular employee type has access to that particular form so when we go and look at this table this has access value is actually what we want to return as yes or no that employee does or does not have access to that form so I'm going to have that field I'm going to make it so that by default whenever we enter in a new entry into this table the default value for that new record is going to be yes and then the last thing I need to do this table is make a primary key and I'm actually going to make this a composite key between those two fields the employee type and form name because ideally we should never have two records with the same employee type and form name in them so you're only gonna have one record with that particular employee type and that particular form name okay so that's going to be my composite key I've got my three my three columns here that I want my table let's go ahead and save it and I'm going to call it Table nine employee access okay so there we go this is table this is the table that's been going to contain the information that relates this particular employee type should or should not be able to open that film and the first value that we're going to do here is we're going to go ahead and set up this relationship that we wanted between the form employee form and that employee that is an administrator so employee type of administrator is going to access to that particular form so the employee type is going to be three the form name is frm employees and does an administrator have access to that form the answer is yes so has access should remain true now I also have employees here that are employee type of two and one and zero I'm going to go ahead and go in here and I'm going to add the employee type of two to have access to frm employees is going to be a value of false so anybody who is an account manager does not have access to the to the frm employees form okay that's we're sitting up here in this record now for everybody else sales representatives and any employees that might be a zero here I'm going to hold off on talking to you about them for just now I just want to see these two records with a three for an employee type which is administrator and two which are account administrators and we can see we have a difference here of has access and does not have access let's go ahead and save that now since I am dealing with a front-end back-end situation for my database I need to go ahead and link this new table that I created here table nine employee access I need to establish a link to my front-end so let's go ahead and do that now I'm going to go to my front-end basis I'm going to go to external data go to the access click on link to the data source brow to my data source which is this file here and open it it's going to prompt me for a password alright and then I'm going to go and select that new table I've created click OK and we can see now I can open up that table that linked table for my front-end and we have access to it now from the front-end okay so let's go ahead and save that again just because I like to save all along the way all right so now that we've got our table created with our relationships in it we've got some data in our table we need to start to do some of the coding and before we can begin on this we have to think a little bit about how we want to present a check so we want to do this check well where do we want to do this check and how do we want to present the results of this check to the user at the time that they try to open up this form so there's a few different options and really I break it down to about three number one you can either do the check when the form tries to open you can do the check when somebody clicks on a button or does some sort of interaction to open the form or three you can even hide all of the interactions available so you can try to hide any buttons that might go to that form let's start off with probably the the safest way the safest one to do first okay and that would be to check when the form is opened when it's being loaded check to see whether or not the user has access to it that way no matter where the user is in the database when they try to open up that form it's going to do the check okay so universally everywhere everywhere they try to open up the form from their going to be checked so I'm going to go to the onload event for the form employees form and this is where I'm going to start from I'm going to create in my form load I'm going to do if now since I have my data that has the information in it about you know which user types have access to this form I need to use some sort of way of opening up that table and checking that has access value for it and the simplest way to do that is to just use a domain aggregate called D lookup that's a deal lookup function you guys are probably very familiar with this function and essentially with the dlookup function you need to return back the value from a field so what is this exp RS string is the field that you want to return the value of and that would be the has access feel that I created there and the domain is the name of the table or you could even use a sequel query here if you wanted some sort of sequel string or our query itself you could actually name a query that you've saved in your Access database but this case it's just Table nine the employee access is the name of the table and then the criteria is a string that you pass in that is essentially a where Clause like if you were doing a sequel query it's basically a where Clause without the where in it the where keyword so that would be where employee type underscore ID is equal to and here we need to think about this because we are checking based upon who's logging into the system what is that employee type that that person is that's logging in and we're going to look into the database table here based upon that employee type but so far we don't really have any way to get that employee type yes it exists but we're thinking about when the user logs in okay that is the point in time where we know who the user is if we just go to the login screen now and take a look at the code behind it when the user clicks on the login button here we should have something that of course goes and does usual check to make sure the user name and the password match up but then after that's been validated this is where you can grab that information about okay now that they've logged in and I know who's logged in what is the employee type and you can even see I've used that here in another part of this code so I have a record set open right now during the login process that gets this employee type ID so I can just go ahead and utilize that and I need to store this information excuse me because the login form is not always going to be available it's not always going to be open this record set that gives me that value is not always going to be there so what I need to do is I need to store this value in some sort of variable that I can recover later do is we're going to use the 10% which is a global collection that access will always make available to you um and I'm going to give it an index name of employee type and in here this is where we're this is the location that we're going to save the employee type that we get after they log in so please type underscore ID so we're going to take the employee type ID of the person that's logging in because the record set as part of this process is moving along the record set to the appropriate record and then gives us the employee type ID of that employee that's logged in and I'm going to pass in this value of employee type ID to this temp VARs collection for the employee type index and it's going to store it in the temp bars now this temp bars is going to be available throughout the entire application and we can ask that that every single time we can ask you know from wherever we are in the database we can always ask the Templars collection for the employee type now one thing about when you're using the temp virus collection is that technically a record set when you return a value such as this employee type from the record set what's actually happening is it is an object that's being returned from the record set and you can specify that you don't want an object but you rather want the value of that employee type so you can do dot value and that will actually specify that the employee type be returned as a value that gets stored into the temp bars just kind of one of those quirky things access doesn't like anything any objects being assigned to this temp bars collection okay so let's go ahead and save that save our progress we're going to just one last time I want to reiterate what's going on here just in case I've lost anybody when the user logs in they click on the login button it's going to go through this record set find the user name make sure they've interests write password and then if all if all of that is correct they've entered the right password in we've got the right user that's login it's going to store the employee type ID value which is from this a table one employees table here it's going to grab this employee type ID value and it's going to store that into this temp VARs collection as the employee type index name okay so we want to save that because we're going to need this temp bars a little bit later here for when we do the check on the form employees form now we can go to onload event again and while we're doing our lookup or do you look up now we have the value of who's logging in we've got the input now we can say where employee type ID equals whatever the value of employee type is when they login okay and we also need to check don't forget we've got this table here Table nine employees access we're checking for we're trying to get this has access value for this value and this value so wherever this and this actually should probably look at this record so we're we're employee type is three and form name is form employees give me that record so we're going to do employee type underscore ID equals whatever the value is or the person who's logging in and we need to do the form name is equal to we're going to do just to make things simpler to me dot name okay so that's the name of the form that's being opened right we're going to do it then and end our if statement so if the user returns a value for the has access value of the of the table from the table Table nine employees access if they do have access when we look at the record where the employee type ID is equal to whatever they have logged in as and the form name is the name of this form which is currently frm employees then if it's true then what do we want to do well if you think about it the form is in the process of loading and since it's in the process of loading whenever it turns out that the user should have access do we really need to do anything now I'd say no we don't really need to do anything why not don't need to stop it we don't need to tell it to continue because it's already in the process of doing it so let's just go ahead and change this from equals true to equals false and what we're really checking then is should the user not have access if they shouldn't have access then do comand close this form okay so we're going to close the form whoops we're going to close this form if the D lookup results in a false return now one other thing you probably need to do is give the user some sort of warning as to why when they tried to open up the form it didn't open right because we don't want them to click on something click on a button that they expect is going to open up a form only to have it closed right away and then not see anything they're going to think the program's broken and the button doesn't work so you need to do some sort of thing like message box you do not have access okay something like that just so that they know hey you don't have the permissions required in order to get to this particular location so let's go ahead and save that let's close all this down okay and let's go ahead and give it a try let's go to the login form now I am a administrator so I have a employee type of three so s Bishop is my login and when I try to log in and I try to open up this form employees form I go ahead and get access I have everything I need I have the employees form open up for me but let's try Jane Doe because remember Jane if we just open up this table again here table employees Jane you an employee type of to which if you recall should not have access right employee type to when they try to open up for employees they should not have access to the form so let's give Jane a try shall we let's go to the login form jeido and when Jane opens it up and then tries to go to the form employees there's our firewall you do not have access okay so she's being prompted she's being told you don't have access the form closes never really even appears like it opens and there you go so you've just prevented successfully whenever somebody tries to open up the form they get blocked and even if I try to click on this button which is supposed to open the form I'm going to get the same issue so I've only had to code that in one place in the frm employees when it tries to load I've only had to put the code in that one place in order to make sure that no matter how a person tries to open the form they're going to get blocked okay now there is let's try taking a look at a Stan so Stan here has an employee type of one and remember we did not program one anywhere here in our table nine employee access so let's see what happens when Stan tries to login let's go ahead and login here s Kurth oops did I goof somewhere i mighta give something up here shoot oh come on now let's try this let's just make things simpler copy that make sure that he has the same password I do sorry about that folks got a little administrative stuff I got to take care of here alright tries one more time let's try to get Stan logged in here oops did you do all right so Stan's going to log in here and when he logs in and he tries to open up employees form here remember we don't have any record here in table nine employees for anyone of an employee type of one so when he tries to go and log in there it is so unless we specify in our table nine employees that you shouldn't have access it's going to give you access well it's not exactly the functionality that I want I don't want to have to specify everybody who shouldn't have access I really kind of want to make it only those people who should have access so I need to fix this code check a little bit I'm going to go back into the design view here go into the onload event and I'm going to use the n-z function which is a function that if dlookup was to return a null value in other words it couldn't find anything on my table and employees access that matched my where clause then null to zero turns that null into by default zero that's what NZ stands for knoll to zero but you can also specify with the NZ function a different type of value so we see this value if null you can specify a different value that you want it to be that it returns so in this case I'm actually going to make it so that NZ function returns false if the value is actually null so there we go let's go ahead and save that and let's try stands login one more time we close that okay so now when Stan tries to open up the employee's form you do not have access so there we go now we have an appropriate function here an appropriate check that looks when the form tries to open and checks to see whether or not the user has access now I could go ahead and take this whole big long thing here let's say I wanted to make it so that the cheque doesn't just happen when the form opens up but also when the user tries to click on this open employees form button okay so I want the button itself to also have a check so when the user goes to click on it by default it just opens up the form but now I want to add that check so if do you look up and oh boy there was all that stuff I could copy and paste it but what if there is a better way to do this okay maybe someplace that we can put this where it makes the check universally available throughout the entire database so that wherever we want to code it it's very simple to get a result I'm going to show you exactly how to do that okay so you don't have to write a bunch of code every single time you want to put this check in we're going to go ahead and go to this module section here and I have a Global's module which is just a module it's just a public place okay and in here in my Global's module I'm going to create a brand new function I'm going to call it public function user access okay and in my function my function is going to return back a bullion result so it's going to return back yes this user does have access or no this user does not have access and we're going to take that code from the employees form this NZ lookup that we've got here and I'm going to copy all of this okay so this NZ lookup has access checking the table and all that good stuff I'm going to go ahead and copy that I'm going to go back to my Global's module and I'm going to paste that into my into my function and I'm going to actually make the user access loops I'm going to make this user access function return back the result of that NZ lookup so we're just going to do user access user access equals the value that gets returned back by the NZ and the lookup okay so whatever this return back is going to be assigned to the user access variable which is returned by the public function as a bullion okay so that's going to make things a lot simpler however me dot name doesn't work does it so we need to pass the name of the form as a variable because again this is in my Global's module a new name is not the name of the form anymore so we actually need to pass it in as some sort of some sort of parameter into this function we're going to do form name as string but I'm also going to take it one step further I'm going to go ahead well now that should be well yeah that should be fine we'll just do that I was going to say let's go ahead temp bars employee type and do it but just a little suction anyway there's really no need to pass that in either but if you wanted to just so I can explain this that you could have done like employee type as integer you could have done it like that and then just replaced employee type with this Templars but since Templars is going to exist as it is I don't think that we need to pass that in as a parameter okay so that should be fine we just need to ask does this user have access to this form but we do need to replace the mean name over here with that new parameter that's going to be passed in so there we go let's go and save that let me do a quick debug yeah everything looks good so now I can go back to the employees form and instead of having this big long thing here let's go ahead and delete that and we're just going to do if Global's dot user access pass in the name of the form which I can now say is frm employees or I could just do me name and now that'll check the Global's or this user access function and return back whether or not that user that's currently logged in does in fact have access to that form that's being passed in and now let's just give that a quick little check save that let's close this main form close the employees form now let's try this again so let's try me first and when I try to open up the employees form it works now let's try doing it with Jane so Jade oh and when Jean tries to open it up you do not have access and one last time let's do let's see what happens when Stan tries to get in again a success so now we don't have to type that big long thing in we've got one thing in our Global's module that has that returns back a true or false boolean for us to tell us whether or not that user should or should not have access okay great so now that that's set up let's talk about some other ways we can present the firewall okay because last time we just did the we did the when it loads up but now I want to do it like I said before on this button here I want to make it so that the check happens when the user tries to open this sucker up and when they do that we're going to need to do another if statement right so if now this time we can use that Global's user access so when the user tries to access what form what is the form that we want them to try to access well that would be the frm employees so let's just pass that in as the name of the form that's trying to be opened and if that is equal to true then go ahead and open the form just do the do command open form okay you could of course put a message here if it doesn't work you could do a false you know if false then you know message box you don't have access okay and then else so you know if it's false if it returns false then pop up at the message box if it is true then go ahead and open up the employee's form so let's go ahead and do that and let's log in as let's try Stan again oops okay so when Stan tries to open up by clicking on the button you do not have access that's actually the button itself prompting you with that not the form anymore okay so now we've got two ways that we're doing that check right it's the same check essentially it's still the same code that's doing and returning whether or not that user has access it's just a different place that you're doing the check at I'm going to show you one last thing and that's I think is really cool to do and that is it really helps enhance the user experience it's if you just say whether or not the button should even be available if the user does or doesn't have access because obviously it's probably better to just not even present them the button if they shouldn't be getting access to it so what we're going to do is when this whole form this form main opens up I'm going to go to the law to the onload event here and in here I'm actually going to set the property of this button here this open employee's form button I'm going to set the visibility property of that button equal to whatever gets returned from our function that we built okay so let's go back here let's do the me dot BTN employees that's that it that's that button okay it's visible property is going to be set equal to either true or false value and that's going to be the value that's returned from our global user access function when they try to access the frm employees okay so now the visibility prop of that button is set based upon whether or not that user access function returns true or false let's go ahead and save that click hit a quick little debug make sure I didn't fat-finger anything and everything looks good just going to close this down I'm going to log in now as me and when I log in I can see the button right let's go to close the form try logging in again but this time I'm going to log in as Jane Doe and you'll see the button is not there and what happens when Stan tries to log in button is not there so the button exists it's just not visible because we're changing the visibility property based upon what type of user they are so there you go there's three different ways you can lock down the the availability of the of a form you can either you know just go right to the form make sure that when it loads up that it does that check to see whether or not the user should have access to the form you can do it when the user tries to click on a button that takes them to the form or maybe some other interaction in your form when that when that interaction happens that would normally open the form check inside of the code first and do that function to check and see whether or not they should have access and then the third thing which just is really good for enhancing the user experience is to when the form loads up that would display the buttons that allow them to click on it just go right into the onload event of that form and set the visibility property of that button or of that menu item based upon the results of that function and what it returns back so there you go I hope you guys enjoyed that I hope this is a very useful video for you that you can use later on and you know like I said if you have any questions if you have any comments or anything that you need please don't hesitate to drop me a line in the comments section of this video and also if you're a fellow and you already know the answer to someone else's question please feel free to drop in your answer and answer any questions that somebody else might have this is really supposed to be a community where everybody helps everybody out so please feel free to to answer other people's questions if I'm not available so there you go please don't forget to Like favorite and subscribe if you like this video and I hope you guys have a wonderful day

Channel: Programming Made EZ

Views: 214,144

Rating: 4.9293976 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, microsoft SQL server, SQL server, programming language, How-to (Website Category), authorization, user authorization, user access

Id: DxRpo496w_c

Length: 38min 6sec (2286 seconds)

Published: Sat Apr 18 2015