|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Track Log On By Richard Rost 
5 years ago
Tracking User Logon & Logoff from a Database In this video, I'll show you how to track each user's log on and log off times in your Microsoft Access database. We'll determine the username and computer name from the Windows system environment variables, log the user in when they start the database, and log them out when the close it. Jason from Fort Payne, Alabama (a Platinum Member) asks: I need an easy way to track what time each user starts their database every morning, and what time they leave to go home at night. We've got a timeclock, but nobody uses it. I figure at least this way I can tell when they start working on their computers. Everyone needs the customer database. LOL MembersMembers will learn how to add up all of the time that each user spends in the database. We'll generate a query showing the total number of minutes spent logged in each day. We'll also get rid of the queries from the last lesson and replace them with SQL statements.
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!
Links
Usage
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, append query, sql, insert into, username, logon, log on, logoff, log off, logging user activity, environment variable, environ, log in, log out, login, logout, log user activity, event tracking, audit users, time tracking, computer name, computername, user name, username
IntroIn this video, I will show you how to track user log on and log off times in Microsoft Access by creating a log table and using append queries triggered with a little bit of VBA code. We will walk through setting up the log table, capturing usernames based on Windows environment variables, and setting up the queries and form events necessary to record when users start and stop using the database. This tutorial is perfect for anyone looking for a simple way to monitor user activity in a shared Access database.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I am going to show you how to track your user's log on and log off times in Microsoft Access. Today's question comes from Jason in Fort Payne, Alabama, one of my Platinum members. Jason says, I need an easy way to be able to track what time each user starts their database every morning and what time they leave to go home at night. We've got a time clock, but nobody uses it. I figure at least this way I can tell when they start working on their computers. Everyone needs the customer database, LOL. I would not say LOL to this, Jason. I would say, hey, employees, use the time clock. I used to have employees myself back in the 90s when I used to sell computer stuff, and I had service techs and staff running around, and they would never use the time clock either. So I started clocking when they logged on in the morning on the computers. Even then, half the time they would come in, log on, and then go take a break and sleep in the break room. Anyways, it seems like this is more of a human resource problem than a computer problem, but I get it. You want to be able to track when they start the database and when they shut it down in the afternoon. So let me show you how to do it. First things first, there are two prerequisites for this video. You need to know what an Append Query is. That's where you take data and add it onto the end of a table automatically. We're going to do that with the log on record and then a log off record into a log table. So if you don't know what Append Queries are, go watch that video first on Append Queries. I'll put a link down in the links section below the video in the description. Click on that and go watch it. Also, you're going to need to know Intro to VBA. This requires a teeny tiny bit of VBA. Don't be scared of it. VBA is very easy to use. I try to make these TechHelp videos without programming for those of you who don't know VBA, but I'm really pushing you to try and learn some VBA. Yeah, you could do this with a macro, but if you're going to go to the hassle of learning macros, you might as well learn a tiny bit of VBA. So don't be scared. Go watch Intro to VBA. It's a nice short video and teaches you all the basics and everything you need to know. So go watch that and then come back here and I'll show you how to do it. Here I am in my TechHelp free template. This is the free download from my website. If you want to go grab a copy, go ahead. I'll put a link down below in the links section as well. The first thing we're going to do is create a log table. Let's go to Create, Table Design. This will be a table that will track the logons and the logoffs. You can use it to track any action in the database that you want. You may want to track when they open up the customer form or the contact form or put an order in. You can put anything you want in here. I cover all that in my security seminar, which I'll talk about later. Let's start off with a log ID. That'll be our auto number. Then we'll have a timestamp, the time it happened. That'll be a date/time, and I'll put in the default value down here: =Now(). Just like that. Next, we'll put a username in here so we know what user that is. Choose short text. Then we'll put an event, which will also be short text. That will be something like logon, logoff, and that kind of stuff. So we have the time it happened, who it was, and what the event is. Let's save that as myLogT and click yes. That will just set up the log ID as our auto number. Let's go ahead and close that. Now, what I'm going to do is every time the user opens up the main menu—which is my startup form—and if you don't know how to set the startup form, go watch the video where I create the TechHelp free template. I set up a main menu and create this as the startup form. It's in your settings. When this form starts up, I'm going to log the user on. When this form closes, I'm going to log the user off. I'm not going to go through getting super intense security with usernames and passwords and all that stuff. I do cover that in my security seminar, but for today, we're going to get a simple username. Now, there are a couple of ways you can do it. I'm going to turn this field here (which currently has the date and time) into the username field. Let's open up the properties for this. Get rid of that Now that is in there and change the name to Username. You can hard code a name in here if you want to. Just go to Data, Default Value, or put it right in the Control Source property. You can put in here "Rick" (="Rick") if you want to do it that way. If you want to distribute a custom front end to each of your users, make an ACCDE file, and split your database, that's fine. You could do it that way. What I like to do is something kind of simple. Since everybody on your network has a unique computer name, I like to make a username for Access that's a combination of the computer name and the name they logged on with. That way, you're almost guaranteed to get a unique username for everybody. Here's how you do it. It's real simple. We're going to go into the properties for the form. Go to Events and find the OnLoad property and hit the ... button. Pick the Code Builder. Your VBA editor will open up. There's mine. I'm going to slide it into view here. I'm going to turn off the Project Explorer. That's okay, we don't need all that. This puts us in the Form_Load event. This is what happens when the form opens up. There is Form_Open, and there is Form_Load. Don't worry about exactly when each runs; they're slightly different, but they both do pretty much the same thing. Here, we're going to figure out the username based on the Windows environment variables. It's not super high-tech, computer secure. Hackers can easily get around this, but assuming that your office users are not computer experts, the username is going to be equal to the environment variable. We are looking for ComputerName and then a dot or a dash or whatever you want to put between them, and then UserName. What does that do? That goes out to Windows and says, "Hey, Windows, tell me the name of this computer and the user that's logged on to this computer." I like to do a combination of the two because you could have Joe logged on on a different workstation and want to know where he is. Also, you might have multiple people on a particular computer. You could theoretically have two different computers with the same username logged on. You may get two different Joes working in the office, but one is on workstation 14, and the other is on workstation 8. This usually gives you a unique identifier; it tells you who it is and where they are. Save this, and if you close the main menu now and open it back up again, look at my username. My computer name is Spock, and my Windows log on name is Amaker. It's short for Amaker. I did not bother setting it up when I configured the computer and just left it, so that's okay. But that is correct. How do you find that? If you open up a command prompt—the good old-fashioned Windows DOS prompt—type in the word SET, you'll see all kinds of environment variables in Windows. You can grab all of these with the environment command that I just showed you. The home path, the home drive (e.g., C:\\Users\\Amaker), is this user's folder. The user's app data, the logon server, how many processors you have, system root (where Windows is installed), your temp directory, and so on. Right there is USERDOMAIN, USERNAME. The computer name is up here: Spock. So you can grab any of these variables you want. Can users change them? Yes, most do not know how, though. Again, if you want super high security, get my security seminar. But this is good enough for most people and most offices. Now that we know the username, and obviously know the time, let's make two queries—append queries—to say, "Okay, when this form opens, run an append query to insert a record into that log table telling the database: Spock/Amaker just logged on." How do we do that? You know how to make append queries. In my append query video in my basic class, I teach that with an append query, you take some data from one table and append it into another one. You don't need to pull data from a table to put it in another table. You can just insert one record at a time with an append query. Here's how you do it. Create Query Design. Close the property sheet. We don't need the add tables. We're not adding any tables to this query. We're going to turn it right into an append query, and we're going to pick our logT as the table that we're going to be appending into. For the first field, we're going to put in the username. What is the username? That's found on Forms!MainMenu!Username. It's in that box on the main menu. If you don't know what this is (Forms!MainMenu!Username), go watch my Form Name video: how to get a value from an open form. I should probably have put that on the prerequisite list. There are two more videos for you to watch: Getting a Value from an Open Form (the Form Name video) and Concatenation (where you put two string values together). That's what I did here with the string concatenation using the ampersand. Go watch those videos too if you don't know what that stuff is. I have lots of videos, and as TechHelp goes on—I think I just passed 200 TechHelp videos now—there are so many videos for free on my website and on YouTube. Check them out. Of course, members get lots more from the extended cuts too. Here, I'm going to create a field called username, which is equal to the Username on that form. I'm going to append it to the username field. Here are all the fields: LogID is an autonumber; we do not have to worry about that. Timestamp is set in the table with a default value; we do not have to worry about that. We just need username and event. What's the event going to be? We're going to make a field called Event: and set it to "Logon" (no extra quotes). We're going to append that into Event. Every time I run this query, it is going to make one record, which is going to be the username and the event. Save this. This will be my LogonQ. Now, while we're at it, let's copy and paste this one. Copy, paste (Ctrl+C, Ctrl+V), LogoffQ. It will be very similar. Right-click, Design View, "Logoff." Save that. Logon, Logoff. Notice that right now our table is empty. There is a blank in there with a timestamp. Let me move this so you can see it when we finally get values in there. I personally hate this little "Click to Add Field" on the end over here. I don't like that at all. I think if you're going to add fields, go into Design View. Now, I'm going to close this and open it up again. Watch this. I'm going to come down here and open up MainMenu. I'm logged on. Looking in the log table—nothing in there. Why? I made the queries but I did not trigger them. Save changes, yes. I did not run them. If I double-click here to run this manually—double-click. Did I not name the field? Let's see here—hold on—username. Okay, open it up—Forms!MainMenu. Oh, look at that. I left this mistake in the video because I want to point it out. I would say 90 percent of all times that people email me and say, "This isn't working right; it keeps popping up 'Enter Parameter Value'," the problem is you have something spelled wrong. Ninety-nine percent of the time, anytime you see "Enter Parameter Value" and you are not expecting it, something is spelled wrong. Can you see it? See what I did? I forgot the F. It's MainMenuF. In fact, I have a whole video on "Enter Parameter Value" and why it comes up. I'll put that down in the link section too. Go watch it. I made a classic mistake, so I will leave this in the video for you to see. It's Forms!MainMenuF. Do the same in Design View for the copied query as well, because you copied the error. Whenever you see "Enter Parameter Value" and you are not expecting it, just like if the main menu was closed and you try to run this, you are still going to get it because Access can't find that value. You have to make sure that this is open, and then when you run this, the append query runs in the background. Nothing appears to happen, but there is your data down in the table. So, before I goofed, let's go back to what I was saying originally. I have to trigger this query to run when this form opens. Go back into that Form_Load event: Properties, Events, OnLoad, ... Right after this, we're going to run the query. It's DoCmd.OpenQuery "LogonQ". If you don't have warnings turned off for action queries, you will get those prompts every time this runs. You are about to run an action query and append one record. You can turn them off on a system level by going into File, Options, then under Client Settings, and find "Confirm Action Queries." I have it turned off. I do not want to be notified every time an action query runs because I rely on them throughout my database for things like this. But, if you're going to be distributing this database to other people—and I assume you are, which is why you want to track people logging on and off—you can't rely on this because you'd have to go around to everybody's workstation and turn this off. We can turn it off inside code with the DoCmd.SetWarnings command. We're going to set warnings to False, and then right away we're going to turn them back on again. You want those warnings on generally, as turning off warnings turns off all system warnings, not just the action queries. It will also turn off delete confirmations. If someone accidentally hits delete and they're going to delete a customer, they will not get a warning message. So, we're going to turn warnings off, then run our query so we do not get bothered with that, and then turn them right back on again. There you go—four lines of code to do that. You could get away with two, but let's say it's four. Save it, come back over here. Let's close the MainMenu and open it up again. Check our table—there we go. Two logons. The logoff event is going to happen when this form closes. That will also get triggered if they shut the database down too. Whenever this form closes, we go over to the Unload event. You can also put it on Close; I like OnUnload because OnUnload can be canceled. We're not going to cover that right now, but there is a Cancel option where you can check a value in some field before allowing the database to close. Here, we're just going to do the same thing. Copy this block of code, paste it in here, and change LogonQ to LogoffQ. Save that. That will also trigger when you go into Design View. So, MainMenu, and close it. Check the log table—logoff, logoff. The only time you really have to worry: this is going to give you valid information every time. If I close the database, it will run that too. If I open up MainMenu, I'm logged on. If I close the database, the logoff code should still run. Open it up again, and there we go—it should have logged me on and off. It logged me off and then back on again when I closed and reopened Access. The only time you will not get a logoff record is if Access freezes, for example. If the system locks up, or Access does not properly shut down. You'll have to check your time logs. If you see someone was logged in for 29 hours, you'll know something went wrong. Generally, this is pretty reliable. If you want to learn more, we've got this big table full of logon and logoff times. In the extended cut for members, I'll show you how to calculate the total minutes between these. Even though these are in separate records, I'll show you how to put them together in one query so you get the logon time and its associated logoff time. We'll calculate the total minutes. From each of those, we can aggregate those together and get the total minutes they were logged on each day. That'll be in the extended cut. I'll also show you how to get rid of all those queries (the logon and logoff queries) and make that into an SQL statement, so we don't have all these extraneous queries floating around the database. Plus, you can use it for other things too. That's covered in the extended cut for members. Silver members and up get access to all of my extended cut videos. There are over 200 of them now. Plus, if you really want to learn how to secure your database, and you want to set up proper logon with a username and password, I have a Microsoft Access Security Seminar. It's over seven hours long, and it covers everything you need to know to properly lock down your database and secure it from unauthorized use. We can manage the workflow of the database—who's doing what—with that log table. We can control who has access to what things. We'll set up users and groups: sales reps, managers, admins, service techs, whatever you want. You'll be able to control what forms, reports, and other items they can access. I'll show you how to properly encrypt your database. We'll also show how to manage workflow, like when they click on "service call completed," log it in that table I showed you. There are all kinds of good things in this seminar. Some more features: separate manager menu, how to lock down the database and encrypt it with a password, and more. That is in my Security Seminar, and I'll put a link to that below in the link section as well. How do you become a member? Click the Join button below the video. After you click Join, you'll see a list of all the different types of membership levels that are available. Silver members and up get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. Platinum members get all the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website, and not just for Access—I also teach Word, Excel, Visual Basic, ASP, and lots more. These free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the primary purpose of the video tutorial?A. To show how to secure an Access database with passwords B. To demonstrate how to track users' logon and logoff times in Microsoft Access C. To teach how to create custom forms in Access D. To discuss how to import data from Excel to Access Q2. What are the two prerequisites the instructor suggests you need before following the video? A. Knowing how to create reports and charts in Access B. Familiarity with Append Queries and Intro to VBA C. Understanding SQL Server integration D. Basic PowerPoint skills Q3. What is an Append Query used for in this tutorial? A. To modify data in existing records B. To delete records from a table C. To add data automatically to the end of a table D. To select specific records to display in a form Q4. Which field is NOT included in the log tracking table created in the tutorial? A. LogID (AutoNumber) B. Timestamp (Date/Time) C. UserName (Short Text) D. Password (Short Text) Q5. How does the instructor suggest generating a unique username for each user? A. By combining the computer name and the user logon name from Windows environment variables B. By using employee payroll numbers only C. By requiring the user to enter their email address each time D. By taking the first three letters of the user's last name Q6. What event is used to trigger the logon tracking in the main menu form? A. OnClick B. OnCurrent C. OnLoad D. AfterUpdate Q7. Where is the logoff tracking code placed in the main menu form? A. In the OnOpen event B. In the OnClick event of a button C. In the OnUnload event D. In the OnDoubleClick event Q8. Why does the instructor recommend turning warnings off before running the append query in VBA, and then turning them back on again? A. To prevent Access from saving changes automatically B. To avoid unwanted delete confirmations C. To prevent action query confirmation pop-ups during tracking D. To disable form validation temporarily Q9. What common mistake does the instructor demonstrate that results in the "Enter Parameter Value" message in Access? A. Forgetting to save the query before running it B. Misspelling the name of a form or field in a query reference C. Using the wrong data type in the query D. Failing to close other forms Q10. How can you see the available Windows environment variables (like COMPUTERNAME and USERNAME) as explained in the video? A. Run the SET command in a command prompt window B. Open the Control Panel and select Environment Variables C. Open Access Options and select System Variables D. Use an Access report Q11. What happens if Access crashes or the system locks up while tracking logon/logoff events? A. The logoff entry may not be recorded B. All log entries are erased C. The user is logged off automatically D. Access generates an error log file Q12. What does the instructor promise to explain in the "extended cut" for members? A. How to add a password to your forms B. How to join logon and logoff records to calculate total minutes logged in per day C. How to backup your Access database to the cloud D. How to print log files Q13. If you want to set up a full-featured security system for your database, what does the instructor recommend? A. Splitting the database by department B. Using the built-in Access encryption tools only C. Taking his Microsoft Access Security Seminar D. Disabling all macros Q14. According to the instructor, what is one risk of turning off warnings globally in Access? A. The database will run more slowly B. Users will not get notified of deletions, potentially deleting data by accident C. Append queries won't run D. It prevents users from printing reports Q15. What does the log table allow you to do besides track logon and logoff? A. Track any user action in the database, such as opening forms or completing orders B. Only track failed login attempts C. Store backup copies of the entire database D. Track Access license keys Answers: 1-B; 2-B; 3-C; 4-D; 5-A; 6-C; 7-C; 8-C; 9-B; 10-A; 11-A; 12-B; 13-C; 14-B; 15-A 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. SummaryToday's video from Access Learning Zone focuses on how to track user log on and log off times in Microsoft Access. This topic came up from a member asking for an easy method to record when each person begins and ends using the database each day, as their traditional time clock system was not being utilized.To address this issue, I want to first point out that while tracking user activity in the database can help, this is ultimately more of a management or human resources issue than a technical one. However, using Access to log when users start and stop using the database is an effective way to at least capture when people begin to work on their computers. Before getting started, there are two important prerequisites you should be familiar with: append queries and basic VBA. An append query allows you to automatically add information to the end of a table – which is what we will use to log each entry and exit event. If you are new to append queries, you should first review my video on the subject. You will also need a basic understanding of VBA, as this solution requires a small amount of code. VBA is not as intimidating as it might seem, and learning it opens up far more functionality than using macros. For anyone unfamiliar, I recommend watching my introductory VBA lesson. In my demonstration, I am working with my free TechHelp template, which you can also download from my website. Our first step is to create a log table to store the user logon and logoff records. This table can actually track any actions you want in the database, such as opening specific forms or placing orders, though today we are focusing just on logons and logoffs. The log table will have several fields: an auto number LogID, a timestamp (with its default value set to the current date and time using the Now() function), a Username field (short text), and an Event field (also short text) to identify whether the action was a logon or logoff. After saving this as myLogT, we can move on to automation. Each time the user opens the main menu (the designated startup form in my setup), we will record a logon event. When the main menu closes, a logoff event will be logged. For the purposes of this video, I am not going to cover detailed security topics like usernames and passwords – that level of security is covered in depth in my security seminar. Here, we will simply record a basic username. There are several ways to determine a username. You could hard code the user's name in a field or use a different approach depending on how your organization distributes front-end files. My preferred method is to build a unique username by combining the computer's name and the user's logon name, both retrieved from Windows environment variables. This technique works well for most offices because it identifies both the user and the machine they are on, which helps resolve duplicate usernames across multiple computers. To implement this, open the properties of the main menu form and use the OnLoad event to set the username by referencing the relevant environment variables. This way, whenever the form starts, the username field is populated with the combination of the computer name and logon name. If you are curious, you can see all available environment variables in Windows by opening a command prompt and typing "SET". This shows not only computer and user names, but paths to user folders, system roots, and more. While users can potentially modify these variables, most people do not know how. Once you have the username, we can create the append queries. The first query, LogonQ, will take the username from the open form and the event text "Logon" and append this as a new record to our log table. The same approach applies for the LogoffQ, except the event will be "Logoff". Make sure the reference to the open form is correct in both queries; otherwise, you could encounter Access's infamous "Enter Parameter Value" dialog, which almost always points to a spelling error in your object names. After creating the queries, you need to link them to the appropriate events in your form. In the Form_Load event, use VBA to suppress action query warnings, run the LogonQ query, and then re-enable warnings. This prevents users from being interrupted by warning messages each time a logon is recorded. Similarly, in the form's Unload event, run LogoffQ to register the user's logoff. Note that this event is also triggered if the main menu closes when the user shuts down Access. The only time a logoff may not be recorded is if Access or the computer crashes. This approach effectively creates a reliable record of logon and logoff times for each user. Occasional anomalies, such as extremely long sessions, can indicate times when Access was not properly closed, but for most organizations, this is not a frequent problem. For those looking to go further, in the extended cut for members, I demonstrate how to pair logon and logoff records to calculate total logged-in time per user, and how to aggregate these totals by day. I also show how to replace the append queries with SQL statements for a cleaner setup. If you are interested in a comprehensive solution for database security, including true username and password management, controlling user and group permissions, encrypting your database, and more, I highly recommend my Microsoft Access Security Seminar. That series covers a full range of security topics suitable for organizations with more demanding needs. Membership offers additional benefits based on your level, including access to extended cut videos, downloadable sample databases, and full beginner and expert courses not just for Access, but for Word, Excel, and other applications as well. All of my TechHelp videos are free and as long as there is demand, I will continue producing them. If you find them useful, please consider subscribing to my channel for updates on new lessons, and if you want notifications by email, join my mailing list. You can find my complete video tutorial with step-by-step instructions covering everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListCreating a log table for user activitySetting up fields for log ID, timestamp, username, and event Using default values for timestamps with Now() Configuring a main menu form as the startup form Assigning a unique username using Windows environment variables VBA Form_Load event to capture logon details Using string concatenation for custom username generation Creating append queries to store logon and logoff events Referencing form controls in append queries Troubleshooting "Enter Parameter Value" errors in queries Running append queries via VBA when forms load and unload Handling Access action query warnings with SetWarnings Logging user off when the main menu form closes Reliability considerations for automated logoff tracking |
||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access username, logon, log on, logoff, log off, logging user activity, environment variable, environ, log in, log out, login, logout, log user activity, event tracking, audit users, time tracking, computer name, username, user name, track user PermaLink Track Log On in Microsoft Access |
