I am running the following Update Query in my program and I am encountering the following Error:

Run-time error '2147217900 (80040e14)':
Syntax error in UPDATE statement

Here is the code i am running

My module contains the following code:

Public UserName As String
Public UpdatePassword As String
Public rsUpdateUserQuery As Recordset

Public Sub runUpdateUserQuery()
Set rsUpdateUserQuery = New ADODB.Recordset
rsUpdateUserQuery.Open sqlUpdateUserQuery, cnConnection, adOpenDynamic
End Sub

This is the procedure that is causing the error above, I am trying to update the User's Passwords in my database. I use a similar UPDATE statement for all other User related fields but for some reason this is the only one causing a problem. As far as I can see they are all the same. The Password and Username fields in my database are both Text data types. The program grabs the user info from the form and put it into the corresponding 'Update___' variables. These variables are then used in the UPDATE statements.

sqlUpdateUserQuery = "UPDATE tblUSERS SET Password = '" & UpdatePassword & "'" & " WHERE UserName = '" & UserName & "' "

runUpdateUserQuery

The issue always happens after executing runUpdateUserQuery.
Any help or suggestions would be greatly appreciated!
Cheers

JUST TRY THIS.

sqlUpdateUserQuery = "SELECT * FROM tblUSERS WHERE UserName = '" & UserName & "' "
Set rsUpdateUserQuery = New ADODB.Recordset
rsUpdateUserQuery.Open sqlUpdateUserQuery, cnConnection, adOpenDynamic

With rsUpdateUserQuery
.Fields("FieldName")=txtName.Text
.Fields(FieldName2")=txtName2.Text
.Update
End With

Thanks so much,

I implemented your recommendation and added 'adLockOptimistic' to my recordset.open procedure and it is working perfectly now.

Cheers!

This article has been dead for over six months. Start a new discussion instead.