Modify Access Table using recordset in Excel VBA

Macro to modify access table using record-set in Excel VBA



Sub update_access_table()
    ' Tools Refrences set microsoft active x object
    ' clear exiting data
    ' run query using where clause , field name and new value
    Call edit_data("select * from tbl_sample where rname ='d'", "rname", "newvalue")
End Sub


Sub edit_data(strQry As String, fieldname As String, newvalue As String)
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim dbpath As String
   
    Set rs = New ADODB.Recordset
    Set cnn = New ADODB.Connection
 
    dbpath = ThisWorkbook.Path & "\database.accdb"
 
    ' create the connection
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open dbpath
    End With
 
    With rs
        .CursorLocation = adUseClient
        .cursortype = adOpenDynamic
        .locktype = adLockOptimistic
        .Open strQry, cnn
        If .EOF Then Exit Sub
        .MoveFirst
        Do Until .EOF
            .Fields(fieldname).Value = newvalue
            .Update
            .MoveNext
        Loop
        .Close
    End With
    cnn.Close
End Sub