How to delete matching rows/records between tables using Access VBA?

Question

I need to delete all rows (records) from a table (Table2) that appear in another table (Table3) that have the same Code (present on Column "Code", with the same name for both tables).

I found a suggested solution in SQL:

DELETE Table2
FROM Table2
INNER JOIN Table3 ON Table2.Code = Table3.Code

My try in Access VBA.

DoCmd.RunSQL "DELETE * FROM Table2 INNER JOIN Table3 on Table2.Code = Table3.Code"

I get

runtime error "Specify the table containing the records you want to delete"

What I got partially working:

DoCmd.RunSQL "DELETE * FROM Table2 WHERE Table3.Code = Table3.Code"

This second code opens a popup asking for a parameter (what I want to match), which works, but I want to do that inputless.

Solution:

Adapting the code Doug Coats provided and "adapting" for Access VBA:

DoCmd.RunSQL "DELETE * FROM Table2 WHERE EXISTS ( SELECT Table3.Code FROM Table3 WHERE Table3.Code = Table2.Code )"
DoCmd.Requery

Added .Requery to remove the #Deleted that comes when deleting the records.

I can't imagine any record where Table3.Code does not equal Table3.Code, and this condition is presented in a DELETE * FROM Table2 as partially working: did any record survive this attempt? — greybeard
– greybeard, Commented May 15, 2021 at 3:59

Doug Coats · Accepted Answer · 2021-05-06 18:24:10Z

1

Im generally not a fan of joining to a table just to check for the existence of some value.

Try using this instead:

    DELETE 
    FROM Table2 t2
    WHERE EXISTS
        (
            SELECT t3.Code 
            FROM Table3 t3 
            WHERE t3.Code = t2.Code
        )

answered May 6, 2021 at 18:24

Doug Coats

7,2179 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lowleopard Over a year ago

Thanks, got the code to work, only had to adapt for VBA as I have little knowledge. I'll be editing the question with the working code!

Collectives™ on Stack Overflow

How to delete matching rows/records between tables using Access VBA?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related