Password Validation in MS Access Logins

Microsoft Access used to have it’s own security system. This was discontinued in Access 2007.
Even then I found it cumbersome to use, so developed my own login and password system.
I store users password in SHA1 encryption, then when a user logs in I SHA1 hash their entered password and compare it with the saved hash. That way even if Ms-Access does get hacked, or an experienced user manages to get “Round the back door” , they still cannot log in as another user. NEVER store passwords in human readable form.
The other feature that has evolved and become more pressing, is ensuring password complexity.
A while back I added some options for password complexity which in it’s current form Is included below.
First – I do not like too many rules being “Hard Coded” into VBA. When I develop a system for someone/a company, it should be them/ their IT dept that sets the rules, and those rules may change in the future.
That is why I include a system settings table with administrative access.
e.g.  A sample System settings table structure.
PK_Setting_ID = Auto-Number, primary key, SettingName = The name of the parameter that will be referred to in Dlookups to this table.
SettingDescription = A text option to explain the purpose of the setting to the users. DO not assume even the best of SettingNames will be self explanatory
SettingText and SettingInteger will contain any actual Settings to be used.

System Settings Table Structure

I create a form to display the Contents of the table for the user to update.

System Settings Form

Fields coloured Yellow are Locked = Yes, and set Tab Stop = No. so the user cannot modify them
Fields coloured White  (In this applications colour scheme) are available to the user to modify.
Number fields default to 0 (Zero) so that there is no possibility of a Null character.

Then in the form where a user can enter or modify their password I run a VBA code to use the  function below in the After Update event handler.
If ValidatePassword(Me.EnterNewPassword) <> True Then
MsgBox (“Passwords must be between ” & MyPasswordMin & ” AND ” & MyPasswordMax & ” characters in length. ” & _
=========================================================================================
Public Function ValidatePasswords(UserPasswordVar As Variant) As Boolean
‘—————————————————————————————
‘ Procedure : ValidatePasswords
‘## Used when a user sets or changes their passwords to ensure passwords meet company complexity requirements
‘## uses System table – tblSettings
‘Date: 20160310
Dim PasswordValidBn As Boolean    ‘
Dim PassValidCriteriaBn As Boolean
Dim PasswordCharCount As Integer
Dim MyPasswordMin As Integer, MyPasswordMax As Integer, MyPasswordSymbols as Integer
Dim MyPasswordMinUpper As Integer, MyPasswordMinLower As Integer, MyPasswordNumeric As Integer

‘###   Lookup the Local Rules on Password Complexity from the tblSettings
MyPasswordMin = Nz(DLookup(“[SettingInteger]”, “[tblSettings]”, “[SettingName] = ‘PasswordMinlength'”),0)
MyPasswordMax = Nz(DLookup(“[SettingInteger]”, “[tblSettings]”, “[SettingName] = ‘PasswordMaxlength'”),0
MyPasswordMinUpper = Nz(DLookup(“[SettingInteger]”, “[tblSettings]”, “[SettingName] = ‘PasswordMinUpper'”),0)
MyPasswordMinLower = Nz(DLookup(“[SettingInteger]”, “[tblSettings]”, “[SettingName] = ‘PasswordMinLower'”),0)
MyPasswordNumeric = Nz(DLookup(“[SettingInteger]”, “[tblSettings]”, “[SettingName] = ‘PasswordMinNumeric'”),0)
MyPasswordSymbols = Nz(DLookup(“[SettingInteger]”, “[tblSettings]”, “[SettingName] = ‘PasswordMinSymbols”),0)

On Error GoTo ValidatePasswords_Err

‘### Check password is within length requirements  MIN / MAX
PasswordValidBn = Len(“” & UserPasswordVar) >= MyPasswordMin
PasswordValidBn = Len(“” & UserPasswordVar) <= MyPasswordMax

‘### If passed first test move on to check if password contains Minimum number of UPPER Case alphabetic characters
If PasswordValidBn Then
PasswordValidCriteriaBn = False
For PasswordCharCount = 1 To Len(“” & UserPasswordVar)
If InStr(1, “ABCDEFGHIJKLMNOPQRSTUVWXYZ”, Mid(UserPasswordVar, PasswordCharCount, 1), vbBinaryCompare) > MyPasswordMinUpper Then
PasswordValidCriteriaBn = True
Exit For
End If
Next
PasswordValidBn = PasswordValidCriteriaBn
End If
‘### Check for Minimum number of lower case alphabetic characters
If PasswordValidBn Then
PasswordValidCriteriaBn = False
For PasswordCharCount = 1 To Len(“” & UserPasswordVar)
If InStr(1, “abcdefghijklmnopqrstuvwxyz”, Mid(UserPasswordVar, PasswordCharCount, 1), vbBinaryCompare) > MyPasswordMinLower Then
PasswordValidCriteriaBn = True
Exit For
End If
Next
PasswordValidBn = PasswordValidCriteriaBn
End If
‘###  Check for Minimum Numeric characters
If PasswordValidBn Then
PasswordValidCriteriaBn = False
For intChar = 1 To Len(“” & UserPasswordVar)
If InStr(1, “0123456789”, Mid(UserPasswordVar, PasswordCharCount, 1), vbBinaryCompare) > MyPasswordNumeric Then
PasswordValidCriteriaBn = True
Exit For
End If
Next
PasswordValidBn = PasswordValidCriteriaBn
End If
‘### Check for Minimum Keyboard symbols characters
If PasswordValidBn Then
PasswordValidCriteriaBn = False
For intChar = 1 To Len(“” & UserPasswordVar)
If InStr(1, “#!$£^&(){}[]<>”, Mid(UserPasswordVar, PasswordCharCount, 1), vbBinaryCompare) > MyPasswordSymbolsThen
PasswordValidCriteriaBn = True
Exit For
End If
Next
PasswordValidBn = PasswordValidCriteriaBn
End If

ValidatePassword = PasswordValidBn
On Error GoTo 0

ValidatePasswords_Exit:
Exit Function
ValidatePasswords_Err:
MsgBox “An Error has occured: & Err.Number & ” At: ” & Erl & ” (” & Err.Description & “) in procedure ValidatePassword.”
Resume ValidatePasswords_Exit
End Function
=====================================================================================
End of VBA code for the function