Syntax Error in Update Statement

Guys... :huh: I'm trying to update data in a recordset. First I use the REPLACE INTO query but I got an error saying "INSERT, SELECT, UPDATE and DELETE statements are expected" or something like that. So I guess REPLACE INTO is not acceptable. I used the UPDATE query in my code and I got a syntax error. I don't know where I messed up, care to tell me? Please help me :confused:

Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\user.mdb;"
Conn.Open
Set recset = New ADODB.Recordset
recset.CursorType = adOpenKeyset
recset.LockType = adLockOptimistic
Set recset = Conn.Execute("UPDATE members SET Password = 'txtnew.Text' WHERE Password = 'txtold.Text' ")
recset.Update
Conn.Close

where txtnew.Text contains the new password and the txtold.Text contains the old password.

"UPDATE members SET [Password] = '" & txtnew.Text & "' WHERE Password ='" & txtold.Text & "'"

Use your ADODB Connection Object when doing DML Commands (INSERT,DELETE,UPDATE). :)


Set Conn = New ADODB.Connection
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\user.mdb;"
With Conn
.Open ConnectionString
.BeginTrans
.Execute("UPDATE members SET [Password] = '" & txtnew.Text & "' WHERE [Password] = '" & txtold.Text & "'")
.CommitTrans
.Close
End With