Updating access data from excel

Posted by / 12-Dec-2019 15:36

Open "SELECT * FROM Table1", acc Conn, ad Open Keyset, ad Lock Optimistic, ad Cmd Table Direct If Not (acc RST.

Move First Else Msg Box "No records in Access table.", vb Information acc RST. Close: Set acc RST = Nothing: Set acc Conn = Nothing Exit Sub End If Do While Not acc RST.

What we need is a method of overwriting the Access data with the data from the Excel spreadsheet.

The column headings are identical in the Access table and Excel spreadsheet.

If it finds it, overwrite the row data with the row data from Excel.

It sounds simple but the only thing I've been able to find on Google is how to just import data and overwrite an entire Access table. You can create a 'Find Unmatched .' query; joining on the key, (to identify/list all keys that occur in the excel sheet, but not in the table).

You could also generate an audit trail, offer 'undo' features and report the updates back to the vendor so they can fix their own data.

Description End Sub Sub Insert Into() 'Declare some variables Dim cnn As adodb. Command Dim str SQL As String 'Create a new Connection object Set cnn = New adodb. Connection String = "Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=Northwind; Data Source=Excel-PC\SQLEXPRESS" 'cnn.

Active Connection = con 'delete all records first if checkbox checked If . Execute "delete from tbl_demo" End If 'set first row with records to import 'you could also just loop thru a range if you want. Close Set con = Nothing End With Exit Sub err H: Msg Box Err.

We can't simply truncate and load the spreadsheet data because it's only some of the data that needs to be updated.

Walking through the scenario, the script or macro would have to compare the key field from the Excel file and match it with the key field in the Access DB.

updating access data from excel-47updating access data from excel-86updating access data from excel-84

If you view the unmatched query before executing it you will see what will be appended. ID, table2FROM table2 LEFT JOIN Table1 ON table2.[ID] = Table1.[ID] WHERE (((Table1. Create a query that joins the spreadsheet to the table you want to update using the unique ID. Change the query to an Update query and pick the columns you want to update (not the PK) and enter the column names from the spreadsheet which will be the source of the data.

One thought on “updating access data from excel”