Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2002
    Posts
    4

    Question Unanswered: "Record cannot be deleted or changed because <table_name> includes related records"

    This old chesnut again...sorry.

    I'm trying to update an Access d/b and, because one of the fields is linked via a relationship to another table, I get the above error when I change ANY field and then try to update. I get it irrespective of whether I use a recordset Update or a SQL 'UPDATE <table_name> SET...'

    If I exclude the field in question (which is a look-up ID) from the SQL UPDATE, the update works, but I don't want to exclude it, since the code is intended to be generic and other tables in the d/b may need this type of field to be updated. The field in question, 'ClubNo', is obviously linked to many other tables in the d/b - I have tried to make the d/b as normalised as possible - so the problem will arise for updates to other tables, too.

    To elaborate, the table is a record of sports club fixtures and the rogue field (in this case I'm trying to fix right now) 'ClubNo' contains an integer indicating which club the fixture pertains to: I have different "clubs" because juniour sections have different funding, coaching arrangements, etc etc. So, it could be that at some point, a fixture may be wrongly assigned to a club and need updating. That's why I can't simply bypass the rogue field - I did have in mind to test for only fields which had changed, but as I say above, these field values _COULD_ also change.

    What do I need to add to the SQL query or, preferrably to the RS update command, to get all the fields updated?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Will you be using this update statement to update several records at a time or only 1 record ?

  3. #3
    Join Date
    Nov 2002
    Posts
    4
    It's only 1 record. I have used almost identical code in Tom Wellige's Universal Table Editor and it seems to work just fine, which is even MORE frustrating! I'm trying to wade through that (I'm looking at v2 since some nice person wiped my v1.4 from our server...) to see why his works and mine doesn't but there's nothing jumping out at me. If you can point me straight there, it'd save me a ton of time and multiple Response.Write statements, trying to decipher what's going on in Tom's code.

  4. #4
    Join Date
    Nov 2002
    Posts
    3
    Hi,

    do you use only a web-based frontend for the database? If yes delete all relations in the database, you donīt need them. With simple SQL (joins, unions,...) you can do everything and you wonīt have any further errors!

    The database is only a container you should never work with if you use other methods accessing it (ASP).

    Bye

  5. #5
    Join Date
    Nov 2002
    Posts
    4
    It's tempting to kludge it up that way i.e. by deleting the relationships, but keeping them helps avoid me and others using "illegal" values.

  6. #6
    Join Date
    Nov 2002
    Posts
    3
    But if you extend your script and include some preventive checks it should be no problem. The auto extension for IDīs you can keep. And if you want to ckeck spelling just include some Javascript. So you can solve everything.
    If you need some help just show me some details of the database an tell me what exactly you want to do.

  7. #7
    Join Date
    Nov 2002
    Posts
    4
    SvKr,

    Sorry, but the whole reason for using the code I have is that I don't have TIME to do a bunch of reworking: the site code uses some of relationships to restrict choices so some of it is in place already

    The code I'm trying to correct will allow other users (update privilege is related to member ID...) to update a results page which at present has no UI. The point is, I don't want to change the d/b design willy-nilly because the likelihood is I'll break the stuff which works and works well.

    Are you able to tell me how to correct this problem or not? As stated earlier, UTE does what I need so I know it can be done. The only thing it doesn't do is list the d/b tables, which I'll need because the other others are not familar with the d/b design.
    Last edited by iannorthwood; 11-29-02 at 04:11.

  8. #8
    Join Date
    Nov 2002
    Posts
    3
    OK,

    this was only an tip. I donīt have another solution for you :-(
    I only use relations in an Access-database if I use an Access-based frontend.

    Bye

  9. #9
    Join Date
    Apr 2003
    Location
    Orange County, CA
    Posts
    1
    Access allows you to set a property from the Relationships that allows cascading updates and deletes.

  10. #10
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Originally posted by Instrument_Tech
    Access allows you to set a property from the Relationships that allows cascading updates and deletes.
    yep.

    Also I see to remeber you need to do
    Code:
    delete from x where z=y cascade
    but this could be Oracle stuff? (or I could have just made it up I really can't remeber?)

  11. #11
    Join Date
    Jan 2008
    Posts
    1

    Wink Tauqeer

    Hi Guys,

    Salaam!

    Instrument_Tech's solution "Allowing relationships to cascading updates and deletes" worked for me as I've got the same exception while updating through C#.
    Thanks for your help.
    Cheers!
    Tauqeer.....

Posting Permissions

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