Access to SQL Server linked table problem

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));


soccerplayerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
soccerplayerAuthor Commented:
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.
0
Leigh PurvisDatabase DeveloperCommented:
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?)
0
Leigh PurvisDatabase DeveloperCommented:
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?


UPDATE T1
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

UPDATE T2
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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.