Update Existing and Add New Access Records from Excel
Question details
Update existing Access records and add new records from a monthly Excel worksheet using a unique position number.

- Product
- Microsoft Access and Microsoft Excel
- Device & OS
- not provided
- Scenario
- A monthly Excel worksheet contains position data that must be merged into an existing Access table while preserving comments and other protected fields.
- Observed behavior
- Matching positions should have only selected fields updated, while position numbers not already in the Access table should be added as new records.
Check that each worksheet row has a unique, nonblank position number and that its data type matches the corresponding Access field. Identify the fields to update, the fields to preserve, and any required fields for new records.
Link Excel and run update and append queries in Access
Use this method for recurring monthly imports when position number uniquely identifies each record.
The examples assume the Access table is named Positions, the linked worksheet is named ExcelUpdates, and both contain PositionNumber and PositionTitle fields. Replace these names with your own table and field names.
The update query writes worksheet values into matching records, including blank values. Check the worksheet first if blanks should not replace existing data.
In Access, choose External Data > New Data Source > From File > Excel. Select the workbook, choose the option to link to the data source, and follow the wizard to link the worksheet as ExcelUpdates.
Choose Create > Query Design, switch to SQL View, and enter: UPDATE Positions AS T INNER JOIN ExcelUpdates AS S ON T.PositionNumber = S.PositionNumber SET T.PositionTitle = S.PositionTitle; Add only fields that should change to the SET clause; leave comments and protected fields out.
Create another query in SQL View and enter: INSERT INTO Positions (PositionNumber, PositionTitle) SELECT S.PositionNumber, S.PositionTitle FROM ExcelUpdates AS S LEFT JOIN Positions AS T ON S.PositionNumber = T.PositionNumber WHERE T.PositionNumber Is Null; Include any other required destination fields and their matching worksheet values.
Test both queries on a copy of the database. In Access, open and run the update query first, then the append query. Check the changed records and new positions before repeating the process on production data.

Prepare Excel Updates with WPS Spreadsheets
WPS Office is a free, lightweight Office alternative with a familiar interface. Open and review monthly Excel worksheets in WPS Spreadsheets, then use Microsoft Access for the linked update and append queries. Its format compatibility helps you move between Office applications.

Frequently Asked Questions
How do I keep comments from being overwritten?
Leave the Comments field out of the update query's SET clause. Only fields listed in that clause are updated by the query.
How does the append query find new positions?
The LEFT JOIN matches worksheet rows to Access records by position number. The WHERE T.PositionNumber Is Null condition selects worksheet rows with no matching Access record.
What happens if the Excel worksheet has duplicate position numbers?
Duplicates can cause ambiguous updates, duplicate additions, or key violations. Check and resolve duplicate position numbers in the worksheet before running the queries.
Can I use the linked worksheet for the next monthly update?
Yes. Update the linked workbook and worksheet while keeping the expected file location, sheet name, and field structure consistent, then test and run the update and append queries again.




