Update Access Table from Excel using VBA

Macro to update access database using Update query in Excel VBA


Sub run_sql()
    Dim sqlquery As String
    sqlquery = "UPDATE tbl_sample SET tbl_sample.rname = ""a"" WHERE [tbl_sample.rname]=""z1"";"
    Call edit_data(ThisWorkbook.Path & "\database.accdb", sqlquery)
End Sub


Sub edit_data(dbpath As String, sqlstring As String)

    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection

    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open dbpath
    End With
 
    ' execute command and close connection
    cnn.Execute sqlstring
    cnn.Close

End Sub