Could not delete from specified table

[cbsm]

OK, this is my problem :

Table1 :
Table1ID (autonumber)
.
. lots of field - all kinds
.
.
Table2ID

Table2 :
Table2ID (autonumber)
.
. other fields - all kind


Query :
select table1ID, Table2ID ,..... from table1,table2 where table1.table2ID= table2.table2ID

(the link is also defined in the relationship (1 to many, enforece relational integrity - no cascades).

Now, if I open the query and delete a row from there - it works fine.
But if I write a delete query like so :
delete * from MyQuery where table1ID=123 and table2ID=456
then I get the error message "Could not delete from specified table".
In fact I tried to run this delete statement from a VB program. As I got this error I tried to run directly from Access (2000), and got the above results.

After reading access help I put in the properties of MyQuery "Unique records" to "Yes" - no effects.

Any ideas ?

 

[earthandfire]

Because of the referential integrity you need to delete records from the many table before deleting them from the one table.

Hope this helps.

 

[cbsm]

OK, I see now what might have been the problem.
First I made a mistake in the problem description earlier on
Query :
select table1ID, Table1FieldX ,table2ID,table2FieldY..... from table1,table2 where table1.table2ID= table2.table2ID

delete * from MyQuery where Table1FieldX=123 and Table2FieldY=456

I rewrote my delete query like so :
delete * from table1 where Table1FieldX=123 and table2ID in (select table2ID from table2 where Table2FieldY=456 ).

I suppose the probleme was the query "didn't know" in what table to delete the record (table1 or/and table2 ?) in the first delete query.
This makes sense to me.
What doesn't is why I could delete the record from the query directly ? Maybe I tried with a "special" record ?

Anyways, I can go on with my application now ...

 

[lespaul]

you can't delete records from a query like you were trying to do, you can only delete records from the table.





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual