Could not delete from specified tables

[RichO]

This seems like it would be a common problem but I tried to search for solutions to this and I haven't been able to find a specific answer. The forum excludes too many of the words I'm trying to use in my search....

I have two joined tables and I want a delete query to delete the specified records in the primary table and all of its related records in the secondary table.

DELETE tb_AccountHeader.*, tb_AccountDetail.* FROM tb_AccountHeader INNER JOIN tb_AccountDetail ON tb_AccountHeader.Index = tb_AccountDetail.Index WHERE tb_AccountHeader.PeriodStart=#1/1/2008#

This gives me the error "Could not delete from specified tables". I tried SELECT DISTINCTROW but that doesn't make a difference. I do not believe that it is an issue with permissions because I can delete records from either of these tables if I remove the join.

Any help would be appreciated.....thanks.

 

[gemma-the-husky]

possibly a problem with cascading deletes - i dont think a query can delete from two tables at once

 

[Brianwarnock]

I cannot help with your problem, however you might like to try searching the forum using Google, the "how" is in the General thread, it threw this up when I tried it.

http://www.access-programmers.co.uk/forums/showthread.php?t=100144

Brian

 

[KeithG]

You need to break this up into the below to queries

Delete tb_AccountDetail.* from tb_AccountDetail Where tb_AccountDetail.Index in(Select Index from tb_AccountHeader Where PeriodStart=#1/1/2008#)


Delete tblAccountHeader.* from tb_AccountHeader where PeriodStart=#1/1/2008#

Or if you set the relationship up for cascade deletes you will only need to use the second query

 

[RichO]

you might like to try searching the forum using Google, the "how" is in the General thread

That's very useful to know. There have been a number of times where I had a hard time finding an answer due to exclusion of words.

Thanks for your replies.