Problem with Delete Query (3086)

[Guest]

Hello...I have been following some of the other questions that deal with this
problem...error 3086 "Could not delete from specified tables"...but I have
not been able to resolve my problem. I have other tables where I use a delete
query and they continue to work fine...just think I am missing something? I
have included the SQL below...all your suggestions are much appreciated.


DELETE DISTINCTROW tblPatientInfo2004.*, tblPatientInfo2004.strStatus,
tblPatientInsuranceInfo.*
FROM tblPatientInfo2004 INNER JOIN tblPatientInsuranceInfo ON
tblPatientInfo2004.autoPatientID = tblPatientInsuranceInfo.autoPatientID
WHERE (((tblPatientInfo2004.strStatus) Like "Inactive*"));

 

[John W. Vinson]

DELETE DISTINCTROW tblPatientInfo2004.*, tblPatientInfo2004.strStatus,
tblPatientInsuranceInfo.*
FROM tblPatientInfo2004 INNER JOIN tblPatientInsuranceInfo ON
tblPatientInfo2004.autoPatientID = tblPatientInsuranceInfo.autoPatientID
WHERE (((tblPatientInfo2004.strStatus) Like "Inactive*"));
--

What are you trying to delete? All patient records where strStatus starts with
Inactive? All patient insurance info for those patients? Both?

Do you have Cascade Deletes set on the relationship between the tables?

John W. Vinson [MVP]

 

[Guest]

Thank you for your reply...I am trying to delete both. I do not have Cascade
Delete...but I will give it a try and see if that works. I'll be back...thank
you.

 

[Guest]

I tried the Cascade Delete, but still got the same message...anything else I
should check? Many thanks again.

 

[John W. Vinson]

I tried the Cascade Delete, but still got the same message...anything else I
should check? Many thanks again.

DELETE tblPatientInfo2004.*
FROM tblPatientInfo2004
WHERE (((tblPatientInfo2004.strStatus) Like "Inactive*"));

should do the job, if tblPatientInfo2004 is the "one" side of an enforced
relationship with cascade deletes. You don't need to mention the insurance
table at all - once you delete the parent record, child records in that table
(and all OTHER related tables that have cascade deletes) will be removed as
well.

Just be sure you really want all this historical information irrevokably
deleted... 'cause you can't get it back!

John W. Vinson [MVP]

 

[Guest]

BINGO!...that was the answer. Thank you so much.