disable/defer constraint until end of transaction

[jesse]

Hi,

Does anyone know if it is possible to temporarily disable a constraint in access? I know that t-sql does not support "DEFERRABLE INITIALLY DEFERRED" but is there a workaround? (short of removing a constraint and then creating it again afterwrds, I wouldn't like to do that for every transaction)

I'm using ADO to connect to the db, but couldn't find anything there either.

any help greatly appreciated.

Jesse

 

[Banana]

I don't think so there's a simple workaround -- what kind of constraints are we talking about?

Maybe if we knew more what you are trying to accomplish, we can find an approach that may not require deferring the constraints.

 

[jesse]

Well, I have a lot of constraints. For example, lets say that I have two tables, table1 and table2, and for every row that is added to table1 there should be two rows added to table2 (with some kind of corresponding key).

Now I have two constraints, one for a maximum of two rows which is fine, but also one for a MINIMUM of two rows, which needs to be deferred to the end of a transaction because otherwise it is impossible to add anything to any of the two tables.

That is only one example, I have a number of constraints. Some of these can be circumvented if you watch the order in which you update or add rows, but a number of them, like the one above are just impossible if you can't defer them in some way.