Data Import & Export

Update Existing and Add New Access Records from Excel

Bushra ParveenBushra Parveen Nov 13, 2026 868 views

Question details

Update existing Access records and add new records from a monthly Excel worksheet using a unique position number.

Update Existing and Add New Access Records from Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Link the worksheet

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.

2
Create the update query

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.

3
Create the append query

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.

4
Test and run the queries

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.

Link Excel and run update and append queries in Access
Check position numbers before appending: The unmatched-record filter excludes position numbers already in the Access table. Duplicate position numbers within the worksheet can still cause duplicate records or key violations, so remove or resolve them first.
Free Microsoft Office alternative

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.

Review position numbers and worksheet fields before importing into AccessOpen and save Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for an easier transition from ExcelWork with a free, lightweight Office suite
microsoft office alternative - wps office

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.