Update Multiple Access Table Columns from Excel
Question details
Update many columns in an Access table using data from an Excel workbook.

- 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.
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.
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.
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.
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.
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.
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.
Return to Design View, select Run, and review the update confirmation before accepting it. Open the destination table and verify the updated values.

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.

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.




