access delete query

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
I have this query

SELECT AbiToMvris_NEW_.[MVRIS CODE], SMMT.[MVRIS CODE]
FROM AbiToMvris_NEW_ LEFT JOIN SMMT ON AbiToMvris_NEW_.[MVRIS CODE] = SMMT.[MVRIS CODE]
WHERE (((SMMT.[MVRIS CODE]) Is Null));

Open in new window


I would like to delete the rows in AbiToMvris_NEW_ table  where the AbiToMvris_NEW_ .[MVRIS CODE] is not in the SMMT table.

always get stumped on these

I want to do this in vba
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,

pls try

Dim MySQL as String
MySQL = "DELETE FROM AbiToMvris_NEW_ LEFT JOIN SMMT ON AbiToMvris_NEW_.[MVRIS CODE] = SMMT.[MVRIS CODE]" & _
    "WHERE (((SMMT.[MVRIS CODE]) Is Null));"
CurrentProject.Connection.Execute MySQL

Open in new window


Be sure to make a backup first

Regards
Trideep PatelProject Lead

Commented:
Try this

Delete A
FROM AbiToMvris_NEW_ A LEFT JOIN SMMT B ON A.AbiToMvris_NEW_.[MVRIS CODE] = B.SMMT.[MVRIS CODE]
WHERE (((A.SMMT.[MVRIS CODE]) Is Null));
Top Expert 2016
Commented:
or try
MySQL = "DELETE * FROM AbiToMvris_NEW_ WHERE [MVRIS CODE] NOT IN (SELECT [MVRIS CODE] FROM SMMT);"

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial