Import Excel Data into Access and Update Duplicate Project IDs
Question details
Import Excel records into Access, updating records with matching Project IDs and adding new records.

- 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.
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.
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.
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.
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.
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.
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.
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.

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.

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.




