Data Import & Export

Import Excel Data into Access and Update Duplicate Project IDs

Olivia MillerOlivia Miller Nov 22, 2026 868 views

Question details

Import Excel records into Access, updating records with matching Project IDs and adding new records.

Import Excel Data into Access and Update Duplicate Project IDs
Product
Microsoft Access and Microsoft Excel
Device & OS
not provided
Scenario
An Excel file contains project data that must be incorporated into an existing Access table.
Observed behavior
The import needs to replace values for existing Project IDs and insert new IDs without causing primary-key violations.
Before you start

Check that ProjectID values are unique and nonblank in both the Excel data and the destination table, and that corresponding fields have compatible data types. Identify required destination fields and any relationships that reference the destination table before running action queries.

Solution 1Recommended

Import into a staging table, then update and append records

Use this method to refresh matching projects while preserving existing records and their related keys.

The examples use Projects as the destination table, StagingProjects as the imported table, and ProjectName and Status as example fields. Replace these names with your own and include every field you want to update or insert.

The update overwrites destination values with staging values, including blanks or nulls. It does not delete projects that are absent from the Excel file.

1
Import the Excel file

In Access, choose External Data > New Data Source > From File > Excel. Select the workbook and import its data into a new table named StagingProjects, using the appropriate worksheet or range.

2
Check the staging data

Open StagingProjects in Datasheet View and verify the field names, data types, and ProjectID values. Use Create > Query Design to check for duplicate or blank ProjectID values and correct them before continuing.

3
Update existing projects

Choose Create > Query Design, switch to SQL View, and run this example after replacing the table and field names: UPDATE Projects AS T INNER JOIN StagingProjects AS S ON T.ProjectID = S.ProjectID SET T.ProjectName = S.ProjectName, T.Status = S.Status; Add an assignment for each field you want to refresh.

4
Append new projects

Create another query in SQL View and run: INSERT INTO Projects (ProjectID, ProjectName, Status) SELECT S.ProjectID, S.ProjectName, S.Status FROM StagingProjects AS S WHERE NOT EXISTS (SELECT * FROM Projects AS T WHERE T.ProjectID = S.ProjectID); Include all required destination fields and omit any AutoNumber field that Access generates.

5
Verify the results

Open the Projects table and check updated and newly added Project IDs. Review related records and any validation errors, especially if the destination has other unique indexes or required fields.

Import into a staging table, then update and append records
Preserve related records: Updating existing rows preserves their AutoNumber keys. Deleting and reinserting rows can break relationships or trigger cascading deletes, so check referential integrity before using a delete-and-replace approach.
Free Microsoft Office alternative

Prepare project spreadsheets with WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface. Use WPS Spreadsheet to review project data and edit Excel files before importing them into Access; WPS does not replace the Access database update process.

Review Project ID values and project fields in a spreadsheet before importOpen and edit Microsoft Excel XLSX and XLS filesWork with a familiar spreadsheet interfaceMove existing Office files into a lightweight, easy-to-use suite
microsoft office alternative - wps office

Frequently Asked Questions

Why use a staging table instead of importing directly into Projects?

A staging table lets you inspect the imported data and separate updates from inserts. Use External Data > New Data Source > From File > Excel to import the file into the staging table first.

What if the Excel file has duplicate Project IDs?

Resolve duplicates in the Excel file or staging table before running the queries. Multiple staging rows for one ProjectID can cause ambiguous updates or duplicate-key errors when appending.

Will the update preserve AutoNumber keys?

Yes, if you update existing rows without changing their AutoNumber fields. The append query should normally omit the AutoNumber field so Access generates a key for each new row.

What does the asterisk in SELECT * mean?

The asterisk means all columns. In the NOT EXISTS subquery shown above, Access only checks whether a matching row exists; it does not insert those columns.

Can I automate the import with VBA?

Yes. In the VBA editor, add a standard module and use DoCmd.TransferSpreadsheet to import the workbook into a prepared staging table. You can then run saved update and append queries with CurrentDb.Execute; clear or recreate the staging data before each import.