Force Change PW 2
By Richard Rost
3 years ago
Enforce Regular Password Updates in Access, Part 2
This is Part 2. In this Microsoft Access tutorial, discover how to enhance database security by enforcing periodic password changes, ensuring users update their credentials every 30 or 90 days. A must-watch for maintaining robust user-level security protocols.
Prerequisites
Recommended Courses
Up Next
Keywords
TechHelp Access
Intro In this video, we continue our look at forcing users to change their passwords in Microsoft Access. I will show you how to calculate the number of days since a user's last password change, prompt them to update expired passwords, and use VBA to validate and update their new password. We will also talk about troubleshooting issues with copied forms and highlight best practices like using Debug - Compile to catch errors in your code. This is part 2.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today is part two of my Force Change Password series. If you have not watched part one yet, you will find a link down below. Go watch it and then come on back.
We now join the program already in progress.
Now we have the date that they last changed their password, which might be never, which is 1/1/90.
Now I'm going to say if today's date minus DateChanged... If you take two dates and subtract them from each other, you get the number of days between them. The bigger dates are the ones further in the future. So, today's date minus DateChanged - if that is greater than or equal to 30 (or 90, or whatever value you want to put in there), then we have to change our date. Must change password. We will do that code here.
We will come back to that. Otherwise, they do not have to do their change. So, if that's the case, we are going to open up the main menu. We will put that there.
Regardless of whether they go to the main menu or the password change form, we are going to close this log on form when all that's done.
Now, in here, what you do is up to you. I like to give them a message first instead of just popping up a box right here. For example, "Password is more than 30 days old. Please change it," or whatever other options you want to put on there. Then, we are going to open up that log on form we just made: DoCmd.OpenForm "PasswordChangeF".
Save it. There you go. There is all the code we need in our log on form for now. I think forever, maybe - I do not know. In the extended cut, we are going to do some extra stuff. I think we are good here for now.
Save this. Always throw on a Debug - Compile. Not run, Debug - Compile.
Am I supposed to look at that? Look at that - variable not defined. "Username = Username."
This brings up an interesting point. Whenever you get this, always take a look up here to see where you are at because this title bar will tell you what form you are in. We are in the password change form. Why am I getting this? Think about it. Why can't it figure out the username?
Pause the video and give it a second to think about it.
Well, if you remember, PasswordChangeF is this guy - the one we made. We copied it. What did I do? I renamed the text boxes on it. So, it does not have a username text box anymore. It is Password1 and Password2, so it cannot compile this because the field does not exist. I did not clear out all the VBA code in here from before.
Now we are going to need a whole different set of code, so I am just going to get rid of all of this. Most of the time when I copy a form, I just delete all the code unless I know I am going to need something.
That is another reason why you want to Debug - Compile, because it catches problems like that. If you were to try to make an ACCDE file out of this, when it tries to make that file, it is going to give you an error message saying, "I can't create an ACCDE file," and you are like, "Why not?"
Well, come in here. The first thing you should do is Debug - Compile. It will show you if there are any syntax errors. It will not make the ACCDE file because it cannot compile it.
That is why I always say run a Debug - Compile every time you are getting ready to leave the VBA editor. Debug - Compile. I wish that was an option you could set, like, every time I close this form: Debug - Compile. Note to the Access team, I have all kinds of ideas.
Let's close this. Let's go over to this form and work on our update button. Right-click, Build Event.
Let's change it. I do not want it to be a log on button anymore. Let's come in here and rename this to Update Password Button, or just Update Button.
Right-click, Build Event.
I will leave the cancel button with the Quit in there, because that just shuts the database. "Oh, I do not want to change my password," just quit.
Here is where we do some basic validations to make sure their passwords are valid.
For this class, all we are going to do is check to make sure that they put in Password1 and Password2, and that they are the same.
In the extended cut, we are going to go into a lot more detail. We are going to add some extra stuff. We are going to require a strong password. I hate this stuff myself, but I get it, I understand. If you are a bank, sure, like PayPal or someone, strong passwords are great. But for my website, what is someone going to do? They are going to log on and watch your classes?
A minimum of four characters, a maximum of twenty, must have one lowercase, one uppercase, a digit, and a special character like an exclamation point or something. Then a button to see the password - that little button you can click on and it will show you what your password is. We will do this in the extended cut.
For now, just some basic validation. First, we are going to say:
If IsNull(Password1) Then
MsgBox "Password1 missing."
Exit Sub
That little colon there is a way you can put two or more commands on the same line. I do not use it often, but that is a good use for it right there. Just give me a message box and get out.
We are going to do the same thing for Password2, so come in here and just change this to "Password2". "Password2 is missing." Exit Sub.
Next up, we are going to check to see if Password1 is the same as Password2. So, we are going to say:
If Password1 <> Password2 Then
MsgBox "Passwords do not match."
If you are smart enough to know that lowercase something and uppercase something technically will show as the same password here, there is an easy fix for that, and that is to change Option Compare Database to Option Compare Binary.
What that will do is it will require that these have to be exactly the same. This takes effect over this entire form's module, but since this is all we are really doing in here, this is fine.
I cover this in a lot more detail in a couple other videos, including my developer classes, but if you want an exact match, then change that to "Binary". You should always have "Option Explicit" in all of your modules and your form modules, so you do not accidentally misspell something.
If we get down here, we are good. Password is good and we can save it. Now, how are we going to save it? With one line of SQL and an update query. This is why you should learn SQL.
You could make yourself an update query if you really want to, but this is something that's actually better done in code.
So,
CurrentDb.Execute
"UPDATE UserT
SET Password = '" & Password1 & "',
DateChanged = Date()
WHERE Username = '" & TempVars!Username & "'"
What I tend to like to do is put these different commands on different lines in here. It just makes it more readable. So you have "UPDATE UserT", then you do this, "SET Password =...", comma, and the next one. Sometimes I indent that in.
Then -
WHERE Username = whatever.
In fact, this is not that long. You could probably put these in the same line. If you have a whole big long list of fields, I do it that way. This looks good. See? Much, much more readable.
"UPDATE this SET that WHERE blah" - same thing with select statements. "SELECT blah FROM blah WHERE blah ORDER BY blah".
That will then update the user's password in the table.
That is going to do it for part two. Tune in to my folks for, guess what, part three. See you then.Quiz Q1. What is the purpose of checking if today's date minus DateChanged is greater than or equal to a certain value (like 30)?
A. To check if the user account has expired
B. To determine if the user must change their password
C. To see if the password matches the username
D. To validate the user's credentials
Q2. What happens if the user's password is older than the required number of days?
A. The main menu is opened
B. The password change form is opened
C. The user is logged out automatically
D. The database is closed
Q3. What is the reason for running Debug - Compile in the VBA editor?
A. It saves all forms and modules automatically
B. It finds syntax errors in your code
C. It optimizes the performance of your forms
D. It creates backup copies of your code
Q4. Why might the code fail to compile after copying a form and renaming controls?
A. Because Access cannot find the new form's location
B. Because the old code still references renamed controls that no longer exist
C. Because the form's name must match the module name
D. Because all code must be deleted when copying forms
Q5. What should you do if Access tells you it cannot create an ACCDE file due to compilation errors?
A. Save the project and try again later
B. Search the entire project for missing references
C. Run Debug - Compile to locate and fix syntax errors
D. Close and reopen the database
Q6. What validation techniques are implemented to ensure passwords are entered correctly in the basic version?
A. Ensure Password1 has at least 10 characters
B. Encrypt passwords before storing them
C. Check that Password1 and Password2 fields are not null and that they match
D. Require passwords to include uppercase and special characters only
Q7. Why might you want to switch from Option Compare Database to Option Compare Binary?
A. To allow users to use spaces in their passwords
B. To require that passwords match case-sensitively
C. To improve database performance
D. To allow numbers in the password
Q8. What is considered best practice when editing a copied form's code?
A. Only change the form's name and keep all original code
B. Delete all code unless you specifically need to keep something
C. Paste code from another form directly without checking controls
D. Leave any errors in the code until runtime
Q9. What does the SQL statement in the password update button do?
A. Deletes the user account for the username entered
B. Selects the user from UserT for review
C. Updates the user's password and DateChanged based on their username
D. Adds a new user to the UserT table
Q10. Why is it recommended to format SQL statements over multiple lines in VBA code?
A. To improve code readability
B. To reduce the size of the database file
C. To make the query run faster
D. To comply with SQL server requirements
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-C; 7-B; 8-B; 9-C; 10-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.Summary Today's TechHelp tutorial from Access Learning Zone continues with part two of the Force Change Password series. If you have not already reviewed part one, I recommend checking it out first to get a solid foundation before proceeding with this material.
At this stage, the system records the date when users last changed their password. For those who have never updated their password, the default value is January 1, 1990. The next step is to determine whether a password change is necessary. By subtracting the last changed date from the current date, you can calculate the number of days since the last update. If this value meets or exceeds your chosen limit, such as 30 or 90 days, it means it is time to require a password change. The logic to enforce this rule is added in the code.
If the user's password is still within the acceptable period, they are simply taken to the main menu. No password update is necessary in that case. Regardless of the path taken, once the necessary action is determined, the log on form will be closed when the processing is complete.
When prompting users to change their password, it is good practice to display a message rather than immediately bringing up the password form. For instance, you might show, "Password is more than 30 days old. Please change it," or a similar notification. After delivering this information, the code opens the password change form that was created earlier.
All the necessary code to control this flow is now in place within the log on form. At this point, it is a good idea to save your work and perform a Debug - Compile in the VBA editor. This step is important because it checks for syntax errors or other code issues that may prevent your database from compiling, especially if you plan to make an ACCDE file later. Running Debug - Compile now will save headaches down the road by highlighting any problems, such as undefined variables or missing form controls.
For example, if you discover a "variable not defined" error such as "Username = Username," it is essential to check which form you are actually working in. The title bar in the VBA editor shows you this information. If you see this error in the password change form but recall that you removed the associated text box and renamed controls for Password1 and Password2, that would explain why the code cannot find the expected field. When copying forms, I recommend clearing out any unnecessary code unless you are certain it will be used.
Once these issues are fixed, move on to working with the update button on the password change form. It is necessary to rename it from a log on button to something more appropriate, such as "Update Password Button" or simply "Update Button." While the cancel button can remain to allow users to exit the process and quit the database if they do not want to change their password, the update button should validate the user's input.
For this lesson, the primary goal is to check that both Password1 and Password2 fields are filled in and match each other. Basic validation is performed to ensure neither field is left blank. If a user omits one, the system issues an appropriate message and halts further processing.
Next, the code confirms that the values in Password1 and Password2 are identical. If they do not match, the user receives a notification about the mismatch. If you want to make password comparisons case sensitive, you should set Option Compare to Binary in your module. I go over the details of this and related best practices in my developer classes and other videos, but it is important to note that this setting helps ensure exact matches and reduces mistakes due to misspelled or mis-cased variable names. Always include Option Explicit at the top of your modules to catch such issues early.
Once the new password entries pass validation, it is time to store the new information in your database. This is done efficiently with a single line of code using an SQL update command, which updates the user's password and the date of change in the user table. Laying out your SQL statements with each part on a separate line increases readability, especially for more complex updates. Even with a simple update like this, clear formatting will help you maintain and troubleshoot your code in the future.
This completes the essential steps for part two of the Force Change Password process. In the extended cut of this lesson, I go into further detail, exploring topics such as requiring strong passwords with minimum and maximum lengths, needing lowercase and uppercase letters, digits, and special characters, and adding a button to display the entered password. These enhancements help provide more robust security where it matters most.
For now, we have covered the basic workflow for checking password age, prompting the user to change it when needed, validating their input, and updating the database. To see the entire process in detail with step-by-step instructions, you can find my complete video tutorial on this topic on my website at the link below.
Live long and prosper, my friends.Topic List Calculating days since last password change
Checking if password change is required
Displaying password expiration message
Opening the password change form
Debugging undefined variables in VBA
Removing unnecessary code from copied forms
Validating input on password change form
Ensuring password fields are not blank
Checking if password entries match
Setting Option Compare Binary for exact match
Updating user's password in table with SQL
Recording password change date in table |
