Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update queries (2002/SR3)

    I'm trying to keep a local table (Candidates Database) synchronized with a web-based MySQL one (candidates_web).

    The update that transfers changes to the local machine from the web works fine.
    Here is the SQL:
    UPDATE [Candidates Database] INNER JOIN Candidates_Web ON [Candidates Database].id = Candidates_Web.id SET [Candidates Database].name = candidates_web.name, [Candidates Database].college = candidates_web.college, [Candidates Database].candidate_number = candidates_web.candidate_number, [Candidates Database].programme = candidates_web.programme, [Candidates Database].ft_or_pt = candidates_web.ft_or_pt, [Candidates Database].notes = candidates_web.notes;

    BUT the update that transfers from the machine to the web delivers an error message about 'key violations'--though It seems to work if you ignore the warning message and tell it to go on regardless.
    Here is the SQL for that
    UPDATE [Candidates Database] INNER JOIN Candidates_Web ON [Candidates Database].id = Candidates_Web.id SET Candidates_Web.name = [candidates database].name, Candidates_Web.college = [candidates database].college, Candidates_Web.candidate_number = [candidates database].candidate_number, Candidates_Web.programme = [candidates database].programme, Candidates_Web.ft_or_pt = [candidates database].ft_or_pt, Candidates_Web.notes = [candidates database].notes;

    Is there something I am doing wrong?

    thanks in advance

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update queries (2002/SR3)

    I would expect to receve an error regarding key violations if I tried to set the key field of two different records the the same value.

    So I would check Candidates_Web to double check which field is set as the primary key. It should be Candidates_Web.id .
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update queries (2002/SR3)

    I'd got that far by looking at other postings--but Candidates_Web.id is indeed the primary key, and nor is the query itself touching that field. It's an annoyance rather than anything else, since the query seems to be working perfectly well ...

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update queries (2002/SR3)

    Without seeing the data I don't know what the problem is. Sometimes a problem is caused by some odd anomaly in your data rather than a problem with the SQL.

    At the moment Candidates_Web is a linked table I suppose. Try importintg the table (just for testing purposes) and see if the problem persists. Try cleaning out most of the data and see if the problem persists. Try just joining the two tables in plain select queries and see if there is anything odd like a record appearing twice or not at all.
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update queries (2002/SR3)

    thanks for this. When I simply import candidates_web, the Update Queries work like a charm. I'd be nervous about removintg the data from the web version--but I notice that I get the same problem if I reduce the number of fields in the update query even to one.

    I can also do an append query without any problems from candidates database to candidates_web.

    This is probably not worth bothering about--it's just pesky

    Many thanks.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update queries (2002/SR3)

    It is often the case that a pesky problem is a symptom of something else wrong that you haven't noticed.

    Having imported the web table, copy the whole file so that you can try out various things without affecting your real data.

    Can you post a cut down version? i.e.with all sensitive data removed.
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Jun 2004
    Location
    Oxford, Oxfordshire, United Kingdom
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update queries (2002/SR3)

    thanks for this. The trouble is that everything works fine once I actually copy the tables in. The problem comes when I try to use an update query to copy from a linked table (basically living in an MySQL database online) into what I would hope would be an exact copy of it stored in the front end--from that I've got more manoeuvre in generating queries. I need to do it this way round, because colleagues need to be able to modify the data stored on line. Granted that this is the problem, I don't think I can post it.

    many thanks for your care on this

    Philip

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update queries (2002/SR3)

    I appreciate that you want to work with a linked table. My suggestion of importing was not meant to replace your real situation, but merely a tool to try to track down the underlying problem.

    If all works OK when you import, it suggests the problem lies with MYSQL, or else with your connection to it. I don't know what else to suggest.

    Can you get Access to update linked MYSQL tables in other contexts?
    Regards
    John



  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update queries (2002/SR3)

    I don't know if this is possible, but if you can find the error number, can't you trap it to ignore it with On Error code?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: update queries (2002/SR3)

    Do you have any indices/constraints set on the web table that are not mirrored on the local table?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •