Access to SQL Server linked table problem

Posted on 2006-04-25
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
    LVL 44

    Accepted Solution

    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

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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Concatination in Sample DB 14 51
    C# primary key 9 47
    Order by 8 21
    Control Updating Records using a Form - MS Access 10 16
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now