Saturday, March 28, 2015

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

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