Access to SQL Server linked table problem

Posted on 2006-04-25
Medium Priority
Last Modified: 2008-01-09
I have an Access query and 2 SQL Server linked tables. When I run the query, I
get the message "Single-row update/delete affected more than one row of a linked
table. Unique index contains duplicate values."

I get this error even if I remove all indexes from the linked SQL Server tables. I am actually trying to update 1000s of rows. Why would it think that I am trying to update a single row?

Here is how my query looks like:

UPDATE T1 INNER JOIN T2 ON (T1.[Fld1] = T2.Fld2) AND
(T1.[Fld3] = T2.[Fld3])
SET T1.Fld4 = Yes, T2.Fld5 = Yes
WHERE (((T1.Fld4)=No) AND ((T2.Fld5)=No));

Question by:soccerplayer
  • 3
LVL 44

Accepted Solution

Leigh Purvis earned 500 total points
ID: 16539608
SQL Server won't allow you to Update two tables at once in an UPDATE statement.
You'd use two statements executed sequentially to achieve the same result.

Author Comment

ID: 16540680
I know that. The SQL Server tables are linked tables in Access. So as far as the update statement above is concerned, both T1 and T2 are in Access. That is not the problem. The problem is the error message I am getting that I mentioned.
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16541395
Hmm - OK but to achieve what you're doing (and it is still SQL Server doing it by the way) Jet, via ODBC, will have to fire a whole mess of crap across the wire.  It's not as if Jet's suddenly in control - it still has to find a way of getting SQL Server to execute all this.  Even if it's by crappy SQL with repeated attempts.
Not efficient (especially if this is for 1000s of rows).

The problem seems to be that it doesn't think you're trying to update a single row.  But that it wants one.
Try limiting the criteria for testing - to one record for one side of the join - and then to one record overall.

(Have you tried not removing all indexes - but leaving a PK only - linking the tables again - and seeing?)
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16541878
If you just give yourself a Passthrough query (or executed through a connection in code) then you'd have native TSQL like the following (which should be more efficient - albeit two statements, and without errors) so is it not worth a try for testing if nothing else?

SET Fld4 = 1
FROM T1 INNER JOIN T2 ON T1.[Fld1] = T2.Fld2 AND T1.[Fld3] = T2.[Fld3]
WHERE T1.Fld4=0 AND T2.Fld5=0

SET Fld5 = 1
FROM T1 INNER JOIN T2 ON T1.[Fld1] = T2.Fld2 AND T1.[Fld3] = T2.[Fld3]
WHERE T1.Fld4=0 AND T2.Fld5=0

(Notice the bit fields conversion from Yes/No to 1 and 0)

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question