Delete Query Problem

[kopy]

I've made dozens of Delete queries, and suddenly I can't get this to work. When I run it, I get a message box that says "Could not delete from specified tables." When I go to the Help for it, is adds "(Error 3086)", and says my tables are marked "Read-Only" and I have to change them. I am the sole owner and creator of this Db, so it's not a security issue. Nothing is tagged as Read-Only.

I have two tables, with a one-to-one relationship based on one numerical ID field. I have them both in the query. I'm trying to delete the fields in table A where the ID field matches table B.

I've tried many combinations: no Join; Inner, Left and Right Joins; pulling the ID field down to the grid with different syntax to make them match ("=", "Like", nothing); even just entering an ID number by hand. As long as that second table is in the query, it won't delete. I even tried replacing it with another table just as a test, and that didn't work either.

The View Results displays exactly the records I want deleted, so I know the link is working and the records are there.

But the only way this works is if table A is the only table in the query, but that defeats my purpose.

I know I've made this kind of thing work before. It can't be that difficult. What am I doing wrong?

Thanks in advance to anyone who can help with this.

 

[scriverb]

When there are multiple tables listed in the row for your query ACCESS cannot determine which tables record to delete. To overcome this you must modify slightly the design so that only one table is listed in the Select when all comparisons are completed. Try using something like this:

DELETE A.*
FROM [i][red]yourtablename[/red][/i] as A
WHERE A.[i][blue]yourIDfield[/blue][/i] IN (Select B.[i][blue]yourIDfield[/blue][/i] from [i][red]yourIDfield[/red][/i] as B
WHERE . . .[i][GREEN]put your joins and where statements to select your records here. . .[/green][/i];

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

The red should be the table name:

DELETE A.*
FROM yourtablename as A
WHERE A.yourIDfield IN (Select B.yourIDfield from [b][red]yourtablename[/red][/b] as B
WHERE . . .put your joins and where statements to select your records here. . .;

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[formerTexan]

Also you may wish to consider setting your table relationships and cascading deletes to eliminate leaving orphan records in subsidiary tables.

Cheers,
Bill