Empower your Security with an Easy-to-Use Contact Management Database

How to create outlook signature

welcome to another tech help video brought to you by accesslearningzone.com I am your instructor Richard Rost in today's video I'm going to show you something people have been bugging me about for a while now and that's how to add simple user level security to your Microsoft access database you want to create a little simple logon form username password boom logs the user in and then you can control what they have access to you can track what they've been doing and so on that's what we're going to show you how to start in part one today's video all right now this is a developer level video so what does that mean that means there's going to be some VBA programming if you've never done any VBA programming before don't worry it's not that hard watch this video first my intro to VBA it's about 20 minutes long I'll teach you everything you need to know to get started you'll need to know what modal and pop-up windows are mostly modal windows and so we can put up a logon form and the user can't click behind it the big granddaddy function D lookup definitely watch this video if you never use dlookup before big big important function we'll need nz2 to convert null values to zero or empty strings you should know how to write if then statements very important right if this then do that otherwise exit the database if they don't type in the right password that kind of stuff and of course message box we're not going to be getting the value from it just uh you know pop it in your face and say uh-uh wrong password all right there's a few other ones but I'll mention them as we're going along uh these are all free videos they're on my website they're on my YouTube channel go watch these first if you're not familiar with any of this stuff and also finally and probably most importantly if you have not yet watched my simple security video go watch this right now in order for the techniques that I'm going to show you to have any possibility of keeping people out of your database you need to do some of the things that I mentioned in this video I'll show you how to hide sensitive objects like hide tables and forms hide the whole navigation pane disable the full ribbon and create an execute only ACC de file if you don't do these things and you might as well not follow the rest of this video because you got to do this stuff all right if you want to lock it down gotta watch this now a disclaimer before we get started this tutorial is going to show you security that's good enough to keep most users out of your database all right you want to keep Pat from over and accounting from getting into the the engineering stuff all right if as long as Pat's not an expert you don't have any problems right an experienced access developer however will be able to bypass the stuff I'm going to show you in this video to learn the best possible way to secure your access database I recommend my access security seminar which does cover things in a lot more detail and go over some more bulletproofing that you can do to your access database we'll talk more about that toward the end of this video and of course if your table data is sensitive then SQL Server is the best bet for that but again we'll talk about that later all right today's tutorials for most of you you know small businesses or small department and you just got you know you got a simple database you want to keep people from messing it up and doing things they're not supposed to do and seeing things they're not supposed to see but you're not worried about someone you know some CIA level hacker getting in there okay I'm going to show you how to lock down your database good enough today all right here I am in my tech help free template this is a free database you can download a copy of my website if you want to and the first thing we're going to do is create a table to store your user information so create table design user ID that's my Auto number and this thing off over here we don't need that all right let's go with username short text password short text are there other things we can add sure but we're going to keep it simple for now all right save this as my user t my user table primary key yep there we go and for now I'm just going to add myself all right I'll do Richard password 599 CD okay close it we're good we've got our simple user table set up now we're going to make a log on form now normally when the database starts up this is our startup form we're going to make a new form which is going to be our logon form we're going to have that be the startup form and then if they log on to that properly then we'll open up the main menu so for now I'm just going to copy the main menu because it's got most of the attributes that I want copy paste control C control V we'll call this the logon F it's log on by the way not log in log on and we're good hit okay now we gotta log on form let's design this guy I am going to delete most of the objects off here that get rid of that save a text box and a button and we can shrink this thing up like so this will be our username all right open up properties change the name from current date to username get rid of the control source and the format and copy it and paste it and now it's the password box password and this will not be called password and we're going to make an input mask for it called password just type in the word password there don't know what an input mask is go watch my input mask video I'll put a link down below all right let's change the forms caption under format instead of that we're just going to call it log on we're going to make this the log on button all right we'll call it log on BTN and we're going to copy and paste this because we need to log on and a cancel because we don't want the user to be able to not get out of this right if they don't know the password well type in a wrong password we'll exit out but all right we'll just call that cancel and that will be my cancel button all right we'll slide these over here make it look nice okay let's maybe give the form a splash of color I like purple I don't know I've just always made logon forms purple I don't know why okay now we don't want the user to be able to get around this form so we're gonna make it a modal form go to other like on the form properties here go to other and then set modal to yes I don't like using pop-up because pop-up where it appears is very unreliable and unpredictable especially on a multi-monitor system so I hate pop-up pop-up can make forms appear outside of the access window so I almost never use pop-up but I do like modal modal basically says you can't click on anything behind me until you close me and that's handy for a logon form we are also going to go to the form properties and we are going to disable the control box and the close button so these you can't just close the form the only way to close the form is by using your buttons okay and if you follow the tips in my secure simple security video they won't be able to go to design view because they can't right click up here and flip the design view so the only way they can possibly get around this is to close using your buttons all right now we're going to set this as the startup form so close that go to your file options current database change the display form from main menu to logon form and hit OK it says you gotta close in your open database that's fine a couple more modifications while I'm thinking about it design view come back into here we're going to make the log on button your default button so go to other set default to yes that means when the user presses enter anywhere on this form in any field it'll push that button cancel we're going to make the cancel button yes so if they hit Escape it pushes that button and this is bothering me that they're not even like that so we're going to just make one more little tweak yeah yeah I know okay how's that maybe make the log on button bigger okay good and you can do those little things in here too you can do like that log on right or at cancel Jesse's little little tricks that make it nice oh and of course tab order I'm going to fix our tab order form design tab order Auto order so it starts off on username password boom okay all right I think we're good with that all right we're gonna do we're gonna do the cancel buttons code first because that's the easy one right click build event now there's a bunch of code in here from the main menu first if you copy a form you're going to get all of its code all right let's delete all of this stuff that we don't need in here don't keep it around if you copy the form we don't need all this stuff here get rid of it all all I need right now is my cancel button click okay what's gonna happen when the user clicks on the cancel button we want to just close the database right simple enough quit that's all you need oops I just bang it on my mic there quit okay okay that's me talking about my hands I just knocked my almost knocked my microphone over it's funny because I'm half Italian and it's like I I talk with my hands even when I'm doing the class I'm sitting here talking with my hands all right so let's save that come back out here all right let's go to the logon form that's going to pop up when the database starts right and seeing it's nice it's modal and it hit the navigation pane for you automatically right if I click cancel boom animation shuts down easy enough all right cool open it up again all right now right I'm right into the logon form now I want to go to the navigation pane so since I haven't made an accde file yet I can still right click and go to design view see that's one that's one of the reasons why you want to give your users a ccde files because they won't be able to do that that's very important okay now we gotta write the code for the logon button essentially what we're going to do is we're going to compare what they typed in these two boxes with the information in our logon table and make sure that they're the same all right we're going to look up the username first to make sure it's in the table if it is it's going to bring back the user ID then we'll use the user ID to get their password out of the table and then we'll compare that to what they typed in the password box easy enough okay here we go log on button build event okay we are going to need two variables so dim ID as a long and PW as a string okay first we're going to look up their ID so ID equals NZ D lookup what are we looking at the user ID from the user table where the username equals whatever they typed in to the username field Okay now what's all that extra stuff well D lookup user ID from the user table where the username equals whatever is in the username field but that's got to be inside of quotes and I prefer to use the double double quotes if you don't know about the double double quotes in a string I got a whole separate video on it I'll put it in the links section down below I like the double double quotes instead of a single quote for a lot of reasons mostly because last names can contain single quotes and it throws everything off whole separate video okay so D lookup is going to return a user ID if it finds that username if it doesn't it's going to have a null value that's where NZ comes in and that'll make it a zero so after this command is run I'll either have a zero or a number representing their user ID if it's a zero that means that user is not found so if ID equals zero then message box user not found quit I like to quit instead of giving them multiple tries it slows them down yeah they gotta restart the database again but if someone's trying to hack you know you don't want to give them you know make it easy for them right if you want to put a counter in here with a little variable and say you got three try that's up to you I just say quit the database let them restart it again all right so at this point we have a valid user ID the username exists in the user table now let's look up the password same thing as before but it's a little simpler this time password equals because we know they're user ID now right NZ dlookup password from the user table where the user ID equals the ID that I just looked up a minute ago all right comma empty string so we're looking up the password from the user table where the user ID equals the user ID that we got earlier this does not have to be inside of quotes why because it's a number so I said it's a little easier you want to use IDs if possible looking up strings is a pain but since password is a string if the password doesn't exist we're going to return an empty string you don't like to deal with nulls I don't like nulls all right now at this point we're either going to have a password whatever's in the table or no password which will be an empty string and that's okay you can set up users without passwords if you want to but we're going to compare it to whatever they typed in so if password is not the same as the password in the table or in the Box whatever then message box incorrect password and guess what quit and if okay so they don't have to have a password but if there is a password in the table they need to enter it now at this point you have a valid user okay so what do you want to do with this information well here's what I'm gonna do I'm going to set either a global variable or something like a temp bar with this information so I know who the user is global variables are nice because it's a variable that you declare up here in a public module right and anybody can use it the problem with global variables is that if your database error has any kind of an error Global variables get reset temp errors do not now I got a whole separate video coming out very soon on tempvars they're really cool but for now just keep in mind that temp vars can store information that's accessible anywhere in the database you can use them in queries you can use them in your VB code they're very very handy okay so we're going to say temp vars to EMP vars give it a name let's call it username equals username dot value you have to specify a value for a text box because temp bars have to be a like a string value they can't be the actual username box itself it's weird but that's just how you have to do it all right so now whatever was in that username box is going to be stored in a variable called username attempt vars all right now now we can do stuff with that anywhere we want so we're going to do something simple for now and then in the next video part two we'll do something a little more complicated for now I'm going to do command open form the main menu F so open the main menu okay and then we're going to close the logon form so do command Dot close AC form comma me dot name I just always use me that name because it's the name of the current form you don't have to remember what the name of the current form is comma AC save yes that's just for me your end user won't be able to save the form design but in case you're doing some work and you forget to save it it'll save it for you all right so it's going to store the username in a temporary variable right in the in the system's memory basically okay we're gonna close we're gonna open the main menu close the logon form all right save it give it a quick debug compile to make sure we don't have any syntax errors we can close that close that let's open the database from scratch you ready open it up there we go all right I'll hit uh I'll hit log on oh usually not found okay back to scratch all right I'll type in Dave enter usually not found oh okay all right one more time all right type in your Richard and 599 CD and I'm in okay now one more thing I'm going to show you and let's let's use that temp bar that we got let's set the main menus box here all right let's set that equal to whatever the temp bar is so design view let's go to this forms on load event that's already something in there all right that's my do startup routine you can ignore that if you want to do it's just some code for me in there and that basically resizes this window to fit my video player or my video recorder so you don't you can ignore that you can poke around there if you want to but I'm going to delete it goodbye and I'm gonna say when this form opens up main menu label that's the name of that box that label dot caption equals temp vars come here I can't type today temp vars username that's the thing we set earlier I really can't type today can I all right so main menu label which is this guy that's the main menu label all right set its caption property instead of main menu I want to see the user's name in there right all right so close that close it save changes yes let's restart the database open it up log in and there you go you can see I know how I've used that username to know who's on and I put it in that box now the user is securely logged on and now we can do things in the database with that information so in the next video part two I'm going to teach you how you can control what forms and reports each particular user and go into and view if you don't want them in the order form you get my order form you don't want them seeing any of the invoicing reports keep them out of there and then in part three I'll show you how to make it so that users can only see the records that they've created so you have a user that has created a particular customer record you can only go back in and see his customers I had a platinum member of mine request that because he only wants his sales reps to be able to view that sales reps customers and not go and look at other sales reps accounts okay now as I mentioned at the top of this video the techniques that I'm showing you are only good to keep most average users out of your database an access expert such as myself will have no problems bypassing the security so if you want to learn real access Security check out my access security seminar I'll show you how to create user and group control so you can set users into different groups you could have like an Accounting Group a managers group and so on or I'm just going to show you a basic user level stuff in this Tech help video all right we'll also create a detailed system log we'll be able to see who's got access to what all right I cover a lot more detail than what I'm going over in today's video so check it out you'll find a link down below also I mentioned earlier if your data is sensitive you may want to consider upgrading it to SQL Server I have an SQL Server online seminar available as well where I show you how to set up an SQL server on aweb platform such as with winhost and you also have the benefit of being able to access that data from anywhere across the internet so it's not just in your office so check that out too all right but there you go there is your user level security part one Tech help video for today I hope you learned something live long and prosper my friends I'll see you next time if you enjoyed this video please give me a thumbs up and post any comments you may have below I do try to read and answer all of them as soon as I can make sure you subscribe to my channel which is completely free click the Bell icon and select all to receive notifications when new videos are posted want to learn more click the show more link below the video to find additional resources and links YouTube does a pretty good job of hiding it it's right down there see this part of the description here right the name the videos up here there's a little show more down there right down the bottom it's kind of hard to find but once you click on that you'll see a list of other videos additional information related to the current topic free lessons and lots more and YouTube no longer sends out email notifications when new videos are posted like they used to do but if you'd like to get an email every time I post a new video click on the link to join my mailing list and you can pick how frequently to get emails from me either as they happen daily weekly or monthly now if you'd like to become a paid member of my channel and receive all kinds of awesome perks click on the join button you'll see a list of all the different membership levels that are available each with its own special perks including my Extended Cut videos access to my code Vault lots of VBA source code in there template downloads and lots more I'll talk more about these Perks at the end of the video even if you don't want to commit to becoming a paid member and you'd like to help support my work please feel free to click on the tip jar link your patronage is greatly appreciated and will help keep these free videos coming I got some puppies to feed but don't worry no matter what these free Tech help videos are going to keep coming as long as you keep watching them I'll keep making more and they'll always be free now if you really want to learn access and you haven't tried my free access level one course check it out now it covers all the basics of Microsoft Access including building forms queries reports and more it's over four hours long you can find it on my website or on my YouTube channel I'll put a link down below you can click on and did I mention it's completely free the whole thing free four hours go watch it and okay okay a lot of you have told me that you don't have time to sit through a four hour course so I do now have a quicker Microsoft Access for beginners video that covers all the basics faster in about 30 minutes and no I didn't just put the video on Fast Forward but I'll put a link to this down below as well now if you like level one level two is just a dollar that's it one dollar that's another whole like 90 minute course level two is also free for paid members of any level including supporters so if you remember go watch level two it's free okay want to get your question answered in a video just like this one visit my tech help page and send me your question there members get priority of course while I do try to read and respond to all of the comments posted Below in the comments section I only have time to go through them briefly a couple of times a month and sometimes I get thousands of them so send me your question here on the tech help page and you'll have a better chance of getting it answered and while you're on my website be sure to stop by my access Forum we've got lots of lively conversations about Microsoft Access and other topics I have a fantastic group of moderators who help me answer questions shout out to Alex Kevin Scott Adam John Dan Juan and everybody else who helps them on the site I appreciate everything you do I couldn't do it without you be sure to follow my blog find me on Twitter and of course on YouTube yeah I'm on Facebook too but I don't like Facebook don't get me started now let's talk more about those member perks if you do decide to join as a paid member there are different levels silver gold platinum and diamond silver members and up get access to all of my Extended Cut Tech help videos one free beginner class every month and some other perks Gold members get all the previous perks plus access to download the sample databases that I build in my tech help videos plus access to my code Vault where I keep tons of different functions that I use the code that I build in most of the videos you'll also get higher priority if you do submit any Tech help questions now answers are never guaranteed but you do go higher in the list for me to read them and if I like your question you got a good chance of it being answered you'll also get one free expert level class each month after you've finished the beginner series Platinum members get all the previous perks plus even higher priority for Tech help questions you get access to all of my full beginner level courses for every subject and I cover lots of different subjects like word excel VBA ASP lots of different stuff not just access these are the full length courses found on my website you get all the beginner ones in addition once you've finished the expert classes you get one free developer class per month so lots of training and finally you can also become a diamond sponsor you'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor you'll get a shout out in the video and a link to your website or product in the text below the video and on my website so that's it once again my name is Richard Rost thank you for watching this video brought to you by accesslearningzone.com I hope you enjoyed I hope you learned something today live long and prosper my friends I'll see you again soon