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

Type your Sql Query In inputbox in excel and import data from access to excel

Type Sql Query in input box and import data to excel from Access Table

Sub import_data()
    ' Tools Refrences set microsoft active x object
    ' clear exiting data
    Dim sqlstring As String
    Sheet1.Range("a1").CurrentRegion.Clear
    sqlstring = InputBox("Enter the Query")
    Call get_data(sqlstring, Sheet1.Range("a1"), 1)
End Sub


Sub get_data(strQry As String, rng_to_paste As Range, fld_name As Boolean)
    Dim rs As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim i As Long
    Dim dbpath As String
    Set rs = New ADODB.Recordset
    Set cnn = New ADODB.Connection
  'change database path here
    dbpath = ThisWorkbook.Path & "\database.accdb"
 
    ' create the connection
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open dbpath
    End With
 
    rs.CursorLocation = adUseClient
    rs.cursortype = adOpenDynamic
    rs.locktype = adLockOptimistic
 
    rs.Open strQry, cnn
 
        If fld_name = True Then
     
        For i = 1 To rs.Fields.Count
            rng_to_paste.Offset(0, i - 1).Value = rs.Fields(i - 1).Name
        Next
            rng_to_paste.Offset(1, 0).CopyFromRecordset rs
        Else
            rng_to_paste.CopyFromRecordset rs
        End If
 
    rs.Close
    cnn.Close
End Sub

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

Append Data to Access Table from Excel

Macro to export Excel range to Access table

Sub export_data()
    ' table to insert, workbook ,range to export
    Call insert_data("tbl_sample", ThisWorkbook, Sheet1.Range("a1:b9"))
End Sub


Sub insert_data(tablename As String, wkb As Workbook, rng As Range)

    Dim cnn As object
    Dim workbookname As String
    Dim sqlstring As String
    Dim rngtoinsert As String
    Dim dbpath As String
    Dim columnnames As String
    Dim columncounter As Integer
    
    Set cnn = CreateObject("ADODB.Connection")

    dbpath = ThisWorkbook.Path & "\database.accdb"
    workbookname = wkb.FullName
    rngtoinsert = "[" & rng.Parent.Name & "$" & rng.Address(0, 0) & "]"
    
    ' extract column/field names
    For columncounter = 1 To rng.Columns.Count
        columnnames = columnnames & "[" & rng.Cells(1, columncounter).Value & "],"
    Next
    columnnames = Left(columnnames, Len(columnnames) - 1)
    
    ' create connection
    cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath
    cnn.Open cnn

    ' add data to access table
    sqlstring = "INSERT INTO " & tablename & "(" & columnnames & ") "
    sqlstring = sqlstring & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & workbookname & "]." & rngtoinsert
    
    ' execute command and close connection
    cnn.Execute sqlstring
    cnn.Close


End Sub