CODEWITHSUNDEEP
sqlvbams-access
lowleopard
lowleopard

Reputation: 17

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

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.

Upvotes: 0

Views: 992

Answers (1)

Doug Coats
Doug Coats

Reputation: 7107

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
        )

Upvotes: 1

Related Questions