SQL to Update User Password

[scullman]

First of all, this is my first post so I am hoping not to screw it up too bad. I am working on setting up a simple login for an access database. The database is retained on a secure internal server, the login is just to help keep other employees from tampering with the data in the database. Through research and testing it out, I was able to get it working. However, when it processes the password change, a prompt appears to re-type in the username. I have a function setup (UserNameWindow s) to get the Windows Username of the employee and am using that as their login ID. My question is, is there something wrong with my SQL statement causing it to verify; or am I simply missing something? My assumption is my 'WHERE' section of the SQL statement is incomplete or incorrect.

Private Sub Command0_Click()
Dim strSQL As String
strSQL = "UPDATE tblUser SET Password = '" & Me.txtNewPW.Value & "' WHERE [UserName] = " & Me.txtLoginID.Value

If IsNull(Me.txtLoginID.Value) Then
    MsgBox "Please enter LoginID", vbInformation, "LoginID Required"
    Me.txtLoginID.SetFocus
 ElseIf IsNull(Me.txtOldPassword.Value) Then
    MsgBox "Please enter password", vbInformation, "Password Required"
    Me.txtOldPassword.SetFocus
 Else
    If (IsNull(DLookup("[UserName]", "tblUser", "[UserName] ='" & UserNameWindows & "' And password = '" & Me.txtOldPassword.Value & "'"))) Then
    MsgBox "Old password incorrect", vbOKOnly, "INCORRECT PASSWORD"
 Else
    If IsNull(Me.txtNewPW.Value) Then
    MsgBox "Please enter a new password", vbOKOnly, "New Password"
 Else
    If IsNull(Me.txtNewPW2.Value) Then
    MsgBox "Please confirm your new password", vbOKOnly, "Confirm New Password"
 Else
    If Me.txtNewPW.Value = Me.txtOldPassword.Value Then
    MsgBox "New Password Cannot Be The Same As Old Password", vbCritical, "Password Violation"
 Else
    If Me.txtNewPW.Value <> Me.txtNewPW2.Value Then
    MsgBox "New passwords does not match", vbOKOnly, "Verify New Password"
 Else
    If Me.txtNewPW.Value = Me.txtNewPW2.Value Then
    Me.txtLoginID.SetFocus
    DoCmd.RunSQL strSQL
    MsgBox "Password Change Successful!", vbOKOnly, "Password Changed"
    DoCmd.Close
    DoCmd.OpenForm "Login"
       
       End If
      End If
     End If
    End If
   End If
 End If
End If
End Sub

[NeoPa]

I'm struggling to see why you need account or password information in the data when you are already using the Windows domain account and allowing it to maintain the password.

However, if your code is failing to do what you require, regardless of why you have that requirement, it would help us to help you if you can explain whereabouts in the code it gives this prompt and why you feel it's not what it should be doing.

[jforbes]

I'm also confused why you would want to do all this, but I have a feeling your error is being caused by missing quotes around the value of Me.txtLoginID.V alue

strSQL = "UPDATE tblUser SET Password = '" & Me.txtNewPW.Value & "' WHERE [UserName] = '" & Me.txtLoginID.Value & "'"

If that's not it, we'll need more to go on.

If you are still in the process of adding the password to the Database, instead you may want to consider adding flags or an integer to the User Table indicating to what parts of the program they have access. Then, on the database startup, lookup the user Record based on the Windows User and load those security flags into global variables. Then you can use those global variables to hide/show/enable/disable buttons, fields and forms. The result is no login form and no passwords.

[scullman]

First of all, thank you for the responses. The reason for the need to "login" would be because everyone who has access to the shared network drive has access to the database; however only about 20 people need to use it or even look at it. I was trying to be proactive before something happened by stopping some of the employees that may wonder into the database out of boredom and start changing information. I have an audit trail setup, so I will be able to tell who is making changes; but would rather avoid that if possible. I realize this may not be the most secure. I am fairly novice with Access VBA and read on some threads adding a password login is a simple route to take. It is not "live" right now, still in development and working on more viable options.

jforbes, I added those quotes and that resolved my issue and am no longer prompted to verify the UserName. I am interested in the addition of flags or integer to the User Table. I will do some research and attempt to build that into the database, it seems like a better option. Thanks for that piece of information.

strSQL = "UPDATE tblUser SET Password = '" & Me.txtNewPW.Value & "' WHERE [UserName] = '" & Me.txtLoginID.Value & "'"

[jforbes]

Glad it's working for you.

The following is some information to help you get started in using the Windows User to enable/disable resources in your database...


There are quite a few versions of this code floating around the Internet to get the currently logged in Windows User:

https://bytes.com/topic/access/insights/653928-function-return-username-nt-login-current-user

So using the above code, the first thing that should be done in the database is to load the User Name into a Global Variable. Then use the Global variable to lookup any permissions that you want to track. The following is a snippet of some code I use to set some flags that are used as permissions (the user name has already been placed in the gUserID Global Variable):

    Dim rst As DAO.Recordset
    Dim sSQL As String
    ' Load Preferences
    sSQL = ""
    sSQL = sSQL & "SELECT TOP 1 * FROM Employees "
    sSQL = sSQL & "WHERE  UserID='" & gUserID & "'"
    Set rst = CurrentDB.OpenRecordset(sSQL, dbOpenDynaset, dbForwardOnly + dbSeeChanges)
        If rst.RecordCount > 0 Then
            gAdmin = Nz(rst![Admin], False)
            gEngr = Nz(rst![Engr], False)
            gManagment = Nz(rst![Managment], False)
        Else
            gAdmin = False
            gEngr = False
            gManagment = False
            Call MsgBox ("You do not currently have permissions to " & gLongAppName & ".  Please see your System Administrator to gain access.")
        End If
    rst.Close
    Set rst = Nothing

Then some code like the following enables/disables buttons based on the permissions:

Private Sub Form_Load()   
    Me.cmdEmployeeMaintenance.Enabled = gAdmin
    Me.cmdPartsMaintenance.Enabled = gAdmin
    Me.cmdAttachmentMaintenance.Enabled = (gAdmin Or gEngr)
    Me.cmdSchedule.Enabled = (gAdmin Or gManagment)
End Sub

On last thing to note, if for some reason Access errors and resets the Global Variables, the Boolean values will reset to false, which will disable everything, which is better than enabling everything.

[scullman]

Thanks a ton jforbes. When I get some time, I will read over that thread you linked, as well read through the code you posted. Again, I greatly appreciate your time and the information you provided to get me going on a better solution. I will be sure to credit you in the coding, once I get this up and running :)

[NeoPa]

Let me just clarify for emphasis. I know this has already been said but it may not have registered fully.

1. The linked thread provides a pre-written function that returns the Windows account of the current user to the project.
2. The Windows account has already been logged on to Windows so no password holding, or even validating, is required.
3. With JForbes' example code you can build this security across your whole system without the user needing to re-qualify as such. It's linked into the Windows account but the user doesn't need to do anything to indicate which that is of course.

[scullman]

Thanks NeoPa. Hopefully this thread will inform others of that method, before they spent so much time creating yet another login environment for their users like I planned on doing.

[Rabbit]

You should be aware that storing your permissions like this in Access makes it very easy to bypass. Anyone can open up the permissions table and change what they have access to.

[scullman]

Thanks Rabbit. I did know this, which is why I am happy to dump this project for the alternate method suggested by JForbes.I was unaware of that method, probably because I was using the wrong key words in my searches...

[Rabbit]

The same issues apply to jforbes method. It requires storing permissions in a table in a simple format.