Others

Update Multiple Access Table Columns from Excel

Natalie TaylorNatalie Taylor Nov 22, 2026 868 views

Question details

Update many columns in an Access table using data from an Excel workbook.

How to Update Multiple Access Table Columns from Excel
Product
Microsoft Access and Microsoft Excel
Device & OS
not provided
Scenario
An Excel file contains metrics for records already stored in an Access table, with matching source and destination field names.
Observed behavior
The user needs to match records by a unique key and map Excel columns to the corresponding Access fields.
Before you start

Check that the Excel workbook has column headers, compatible data types, and a key that uniquely identifies each source record. Confirm that the corresponding Access key identifies the intended destination record, and test the query on a copy of the database before updating production data.

Solution 1Recommended

Import Excel data and run an Access update query

Use this method to update existing Access records across multiple columns using a shared key.

Matching field names make the columns easier to identify, but Access still requires you to join the tables and specify the source for each field you want to update.

1
Import the workbook

In Access, choose External Data > New Data Source > From File > Excel. Select the workbook and import the relevant worksheet into a new temporary table, using the first row as column headings if appropriate.

2
Create a query and join the tables

Choose Create > Query Design, add the destination table and temporary table, and drag the unique key field from one table to the matching key field in the other.

3
Choose the fields to update

Add the destination columns to the query grid, then select Query Design > Update. In each column's Update To row, enter the corresponding source field, such as [TempTable].[Metric1]. Repeat for each column.

4
Check the matches

Switch to Datasheet View to inspect which destination records match the temporary table. Verify the key, field mappings, and values on a test copy before making changes.

5
Run the update

Return to Design View, select Run, and review the update confirmation before accepting it. Open the destination table and verify the updated values.

Import Excel data and run an Access update query
Existing records only: An update query changes matching records; it does not add missing records. Use a separately tested append query if you also need to insert new records.
Free Microsoft Office alternative

Prepare Excel source data with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for reviewing and preparing spreadsheets before importing them into Access. Its Excel format compatibility makes it easy to move existing workbooks into your workflow, although the Access update query must still be run in Microsoft Access.

Review column headers and key values before importing spreadsheet data into AccessOpen and save Microsoft Excel formats such as XLSX and XLSUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Do matching column names update Access fields automatically?

No. Matching names help you identify corresponding fields, but in Query Design you must join the tables and enter a source field in the Update To row for each destination field.

What happens if an Excel record has no matching Access record?

An update query does not insert it. If you need to add missing records, create and test a separate append query, mapping the source fields to the destination fields.

Why is a unique key important for the update?

The join uses the key to identify which source data belongs to each destination record. Duplicate or incorrect keys can produce ambiguous matches or update the wrong records, so check both tables before running the query.

Can I preview the records before running the update query?

Yes. In Access Query Design, switch to Datasheet View to inspect the records matched by the join. Test the full update on a copy of the database and verify the results before using it on production data.