Updating MS Access record using ADO with Excel VBA

aditbaco

New Member
Joined
Jul 15, 2013
Messages
20
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 :

VBA Code:
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
L

Legacy 456155

Guest
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.

VBA Code:
    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

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows
To run an action query, e.g. UPDATE, you should be using Command instead of Recordset and Execute instead of Open
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,811
Put square brackets around each column name:
VBA Code:
        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

New Member
Joined
Jul 15, 2013
Messages
20
Put square brackets around each column name:
VBA Code:
        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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,792
Messages
5,655,332
Members
418,190
Latest member
Timex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top