OK, this seems silly.
I have a query which is updateable when viewed as a table in Access (can insert, delete, update). Using queries, I can insert and update, but not delete.
A delete query against this query gives the error 'Could not delete from specified tables', even though I can do the same thing by viewing qb in datasheet mode, selecting all rows and pressing Delete.
Here's pseudocode-SQL for a simplified version of the system with the problem:
CREATE TABLE a (
a_id AutoNumber PRIMARY KEY,
CREATE TABLE b (
a_id Number REFERENCES a (a_id) ON DELETE CASCADE,
CREATE Query q AS
SELECT a.a_id AS a_a_id, a_data, b.a_id as a_id, b_id, b_data
FROM a INNER JOIN b ON a.a_id = b.a_id;
Now, you can delete from q in datasheet mode, but not with something like:
DELETE FROM q;
How can I programmatically delete from q? Or do I have to generate a page of SQL in all my scripts to do what q is meant to do?