Specify the Table containing the records you want to delete (1 Viewer)

[Yahyeh]

Hi.

Im currently using MS Access 2003.

I'm experiencing this error: Specify the Table containing the records you want to delete when deleting a records.

I have 2 tables. Table A is my reference IDs to delete from Table B.
When I link now this 2 tables, then run the query, it prompts me Specify the Table containing the records you want to delete, yet I have already link the Table A which is my reference IDs to delete in Table B.

How to resolve this?
Here's the syntax from MS Access (SQL View)

DELETE DISTINCTROW TABLE_B.ID_A
FROM TABLE_A INNER JOIN TABLE_B ON TABLE_A.ID_A = TABLE_B.ID_A;


Thank You.

 

[Galaxiom]

Access won't delete from a join.

A useful workaround is to flag the records to be deleted using an Update query by putting an out of scope value into one of the fields of the records to be deleted then use that flag in the Delete query.

 

[jdraw]

I don't like to disagree with Galaxiom, but in this case I will.

I delete records from joined tables, but you have to be quite explicit with the syntax.
You identify the table from which records will be deleted first.

Here is a sample from my database showing the syntax.

DELETE MainTable.*
FROM MainTable
INNER JOIN DuplicateObjectNumbers
ON MainTable.ObjectId=DuplicateObjectNumbers.ObjectId
WHERE MainTable.ObjectNumber = DuplicateObjectNumbers.objectNumber ;

Below, I have attempted to put your query iinto the same format as my working example.

DELETE TABLE_B.*
FROM TABLE_A
INNER JOIN TABLE_B
ON TABLE_B.ID_A = TABLE_A.ID_A;

Also, when you do a DELETE query, you DELETE complete records -- NOT individual fields. If you want to DELETE a single field, you should use an UPDATE query.

 

[Galaxiom]

By all means jdraw please disagree. I am no doubt carrying around several other myths due to early experiences.

I rarely delete records and even less when the criteria is on a join so I never really got to the bottom of how it is done properly before I got tired of Access saying "No" and used my workaround.

 

[jdraw]

Having reread my post just now, I believe my attempt with your tables is incorrect. You are deleting from Table_B
It should be, following my sample, with Table_B identified in the From first.

DELETE TABLE_B.*
FROM TABLE_B
INNER JOIN TABLE_A
ON TABLE_B.ID_A = TABLE_A.ID_A;

Sorry for not checking my post more thoroughly.