INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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:

Quote:

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:

CODE -->

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.

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

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

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

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

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

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

Duane
Hook'D on Access
MS Access MVP

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close