Update Query Error: Single-Row update/delete affected more than one row of a linked table.

[Denae]

I have an access database that has linked SQL tables. One of the tables (dbo_EmployeeInformation) has employee records that occasionally need to be updated. I have an extract of current data that I have tried linking into the database as well as creating a table in the database where I paste the information.

I don't want to replace the entire content of dbo_EmployeeInformation, because I would loose historical data for employees that have left the company, I just need to compare the new data with the old to see if anything changed (Name, Address, Manager, etc) and update dbo_EmployeeInformation with these changes.

I created an update query that relates the two tables on their primary keys:

[dbo_EmployeeInformation].[strEmployeeID] = [tblEmployeeInformation_Update].[Employee ID]​

When I run the query I get the error:

Single-Row update/delete affected more than one row of the linked table. Unique index contains duplicate values.

I have checked both tables and there are no duplicates in the primary key fields.

Here is the query I am using:

UPDATE dbo_EmployeeInformation INNER JOIN tblEmployeeInformation_Update ON dbo_EmployeeInformation.strEmployeeID = tblEmployeeInformation_Update.[Employee ID] SET dbo_EmployeeInformation.strActiveStatus = [tblEmployeeInformation_Update].[Active Status], dbo_EmployeeInformation.strOnLeave = [tblEmployeeInformation_Update].[On Leave], dbo_EmployeeInformation.strEmployeeName = [tblEmployeeInformation_Update].[Employee Name], dbo_EmployeeInformation.strFirstName = [tblEmployeeInformation_Update].[First Name], dbo_EmployeeInformation.strLastName = [tblEmployeeInformation_Update].[Last Name], dbo_EmployeeInformation.strMiddleName = [tblEmployeeInformation_Update].[Middle Name], dbo_EmployeeInformation.strPrimaryAddressFull = [tblEmployeeInformation_Update].[Primary Address - Full], dbo_EmployeeInformation.strAddress1 = [tblEmployeeInformation_Update].[Address 1], dbo_EmployeeInformation.strAddress2 = [tblEmployeeInformation_Update].[Address 2], dbo_EmployeeInformation.strHomePhone = [tblEmployeeInformation_Update].[Home Phone], dbo_EmployeeInformation.strJobTitle = [tblEmployeeInformation_Update].[Job Title], dbo_EmployeeInformation.strLocationCode = [tblEmployeeInformation_Update].[Location Code], dbo_EmployeeInformation.strLocationName = [tblEmployeeInformation_Update].[Location Name], dbo_EmployeeInformation.strDirectManager = [tblEmployeeInformation_Update].[Direct Manager], dbo_EmployeeInformation.strHRBusinessPartner = [tblEmployeeInformation_Update].[HR Business Partner];

Any help would be greatly appreciated.

 

[dhookom]

When you linked to the SQL Server, did Access recognize the primary key? Look at the table design of both tables and confirm there is a key by the field names.

Duane
Hook'D on Access
MS Access MVP

 

[Denae]

Yes, both tables have the Employee ID fields designated as primary.

 

[dhookom]

Does the SQL Server table allow Nulls in the primary key? Are there any Nulls?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Aside from your issue, your UPDATE query looks 'dangerous' (to me) - there is no WHERE part to it, so if you run it, you will update ALL records in your dbo_EmployeeInformation table with the same information.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.