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?
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.
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).
If you need some help just show me some details of the database an tell me what exactly you want to do.
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.