How can I update an Access Table from Excel data?

[JumboCactuar]

Hi,
I have an 8 column table in access named DATA1 which I pull into Excel based on date.

Example (SELECT * from DATA1 where EDAT = " '20181108'")

Does anyone know if it's possible with VBA to edit the pulled data and update the access table with new values ?


Appreciate any help

 

[alansidman]

Why not just create your table in Excel and link it to Access. When you do it this way, any changes you make to the table in Excel is automatically synced to Access.

 

[JumboCactuar]

Why not just create your table in Excel and link it to Access. When you do it this way, any changes you make to the table in Excel is automatically synced to Access.

Thanks for the reply
I looked into this option but it doesn't allow you to add records directly into the database which I need also.

 

[alansidman]

I just test this and I can add records to the Excel workbook and they are immediately updated in the Access Data Base. So, I believe that either you and I are not on the same page or your sources are mistaken. I urge you to test it out yourself with a test workbook and test DB to satisfy yourself. Then try it in your production Database.

 

[JumboCactuar]

Yes that works, though I need to add records from a non-linked workbook

 

[alansidman]

Import the non-linked workbook and as long as it is in the same format as the linked table, you can append the records. Alternatively, you could cut and paste the records from the non-linked to the linked and they will update.

 

[JumboCactuar]

In my SQL sheet, I first pull data from access based on date then modify the records. Unsure if I can do the same with a linked table

I think this is close to what I need but don't quite understand it yet

Public Const Conn As String = "Data Source=Path\Database.accdb;"
Private Sub Export_Click()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim ssql As String, tbl As String
Dim ID As Integer

tbl = "Table"
ID = IDLoan
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & Conn

If IDLoan = "" Then     'IF NEW
    rs.Open tbl, cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rs.AddNew
Else                    'If Existing
    ssql = "SELECT * FROM " & tbl & " WHERE " & tbl & ".ID=" & ID
    rs.Open source:=ssql, ActiveConnection:=cn, CursorType:=adOpenKeyset, _
        LockType:=adLockOptimistic, Options:=adCmdTableDirect
End If
With rs
    .Fields("FieldName1") = [A1]
    .Fields("FieldName2") = [C3]
    .Fields("FieldName3") = [C4]
    'repeat for all required fields
    .Update
    .Close
End With
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

From
https://www.mrexcel.com/forum/excel-questions/911978-updating-record-access-excel-vba.html

I only need to modify the 8th column
Column A (ID) is the primary key