Why doesn't this UPDATE statement work?

[VBGolfer]

This is frustrating, I'm almost sure that the syntax of my UPDATE statement is correct, but I keep getting the Invalid Syntax error.

Here is the function...

Private Function ChangePassword(ByVal Password As String, ByVal SalesRep As String) As Boolean

Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\WebData\OrderRequest\OrderRequest.mdb"
Dim strSQL As String = "UPDATE Passwords SET Password = " & Chr(39) & Password & Chr(39) & " WHERE SalesReps = " & Chr(39) & SalesRep & Chr(39)

Dim objConnection As New OleDb.OleDbConnection(strConnection)
Dim objCommand As New OleDb.OleDbCommand(strSQL, objConnection)
Dim objTransaction As OleDb.OleDbTransaction

Try

objConnection.Open()
objTransaction = objConnection.BeginTransaction
objCommand.Transaction = objTransaction
objCommand.ExecuteNonQuery()
objTransaction.Commit()
ChangePassword = True

Catch oleDBException As Exception

lblException.Visible = True
lblException.Text = oleDBException.Message & vbCrLf & oleDBException.Source
ChangePassword = False

Finally

If Not objConnection.State = ConnectionState.Closed Then
objConnection.Close()
End If
objCommand = Nothing
objConnection = Nothing
objTransaction = Nothing
End Try
End Function

Thanks,

Sean

[VBGolfer]

Sorry,

I should mention that the table name is Passwords and the column name is Password and SalesReps...

My SQL statement looks like so...

strSQL = "UPDATE Passwords SET Password = 'blue' WHERE SalesReps = 'Sean'"

[fredrik]

Have you tried running the sql-string directly in Access? does it work there?

[VBGolfer]

I had to add Passwords.Password and it worked fine.

TableName.FieldName

Thanks

[Shawn N]

In this bit of coding

SET Password = " & Chr(39) & Password & Chr(39) & " WHERE

You can do

SET Password = ' " & Password & " ' WHERE

for the sake of readability.