Saturday, March 28, 2015

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
        Do Until .EOF
            .Fields(fieldname).Value = newvalue
    End With
End Sub

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...