Updating MS Access record using ADO with Excel VBA

[aditbaco]

Hi All, I'm trying to create a Login userform where a user may change his/her password in Access record, but it return Runtime Error -2147217900 (80040e14) - Syntax error in UPDATE statement. While the same update statement is working just fine if I try it directly in MS Access query.

My code as below :

Sub User_CP(user_id As String, Password As String)
    Dim que As String

    Dim con As New ADODB.Connection
    Dim addData As New ADODB.Recordset
            
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\database\database.mdb"
  
    If frm_CP.TextBox1.Value <> frm_CP.TextBox2.Value Then
        MsgBox "Pw sama!!"
    Else
    
    Set addData = New ADODB.Recordset
    que = "update [USER] set Password= '" & frm_CP.TextBox2.Value & "' WHERE User_Id = '" & Sheet4.Range("User_Id") & "'"
    addData.Open que, con, adOpenDynamic, adLockOptimistic
    addData.Update

    End If

    addData.Close
    con.Close
    
End Sub

Kindly help which of above code is wrong? I'm using MS Excel 2016 and MS Access 2016.

 

[Legacy 456155]

I'm not sure that you can use anything by a SELECT statement to open a recordset. I could be wrong though as I've always just used the connection unless I needed a record set. In any case, there's no need to create a recordset; just execute on the connection along these lines. Untested. If you have a problem, I'll test it out.

    Dim que As String
    Dim con As New ADODB.Connection
    Dim recordsAffected As Long
          
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\database\database.mdb"

    If frm_CP.TextBox1.Value <> frm_CP.TextBox2.Value Then
        MsgBox "Pw sama!!"
    Else

        que = "update [USER] set Password= '" & frm_CP.TextBox2.Value & "' WHERE User_Id = '" & Sheet4.Range("User_Id") & "'"
        con.Execute que, recordsAffected, adcmdtext
        If recordsAffected <> 1 Then
            MsgBox "problem"
        End If
    End If

    con.Close

 

[Norie]

To run an action query, e.g. UPDATE, you should be using Command instead of Recordset and Execute instead of Open

 

[John_w]

Put square brackets around each column name:

        que = "update [USER] set [Password] = '" & frm_CP.TextBox2.Value & "' WHERE [User_Id] = '" & Sheet4.Range("User_Id") & "'"

The addData.Open should work, but delete the addData.Update and addData.Close lines.

 

[aditbaco]

Put square brackets around each column name:

        que = "update [USER] set [Password] = '" & frm_CP.TextBox2.Value & "' WHERE [User_Id] = '" & Sheet4.Range("User_Id") & "'"

The addData.Open should work, but delete the addData.Update and addData.Close lines.

this is what I'm missing. It's working now. thank you so much @John_w