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?

Will you be using this update statement to update several records at a time or only 1 record ?

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.

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

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.

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.

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.

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

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
but this could be Oracle stuff? (or I could have just made it up I really can't remeber?)

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