Link to home
Create AccountLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

access delete query

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
Avatar of Rgonzo1971
Rgonzo1971
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
Avatar of Trideep Patel
Trideep Patel
Flag of India image
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));
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer