Delete from table does not work.

[LarryRobert]

I am trying running a select query to tell me which records need to be deleted from a table based on a certain set of criteria. Once this is done, I have converted the select query to a delete query since I just want to run the same query in a delete mode. However, when I run the query, I receive the following message: "Cannot Delete from Specified Tables". The help for this message states possible security conflicts or a read only table. None of these are the case. All I am trying to do is delete the records based on my original selection criteria which worked fine in a select mode.

 

[MoLaker]

Can you post a copy of your SQL? I'm assuming you are using a query definition. Go to the to View on the top menu in design mode and select SQL View. Copy and paste the SQL into a posting.

Since you say the error message is

Cannot Delete from Specified Tables

it makes me suspicious that your query uses joins in some fashion which might make the result not updateable.

 

[LarryRobert]

MoLaker,

Here is the query.

DELETE [Current Period Reporting Table].*
FROM [Current Period Reporting Table] INNER JOIN [Deletion Table] ON ([Current Period Reporting Table].[Pharmacy Number]=[Deletion Table].[Stla Paid Claims_Pharmacy Number]) AND ([Current Period Reporting Table].[Date Filled]=[Deletion Table].[Stla Reversed Claims_Date Filled]) AND ([Current Period Reporting Table].[Paid Date]=[Deletion Table].[Stla Paid Claims_Paid Date]) AND ([Current Period Reporting Table].[RX Claim #]=[Deletion Table].[Stla Paid Claims_RX Claim #]) AND ([Current Period Reporting Table].[Claim Sequence #]=[Deletion Table].[Stla Paid Claims_Claim Sequence #]);

Thanks for looking at this.

 

[MoLaker]

Well, I don't see anything there that would prevent deletion. Where is this database located? Is it on your local system or on a network?

Can you delete any records manually from the [Current Period Reporting Table] - with no joins or anything?

 

[LarryRobert]

Yes, I am able to delete the records from the table manually. It is on my local hard drive, so I am not sure why I can't delete.

 

[GingerR]

try some troubleshooting...make a copy of the table so you don't mess anything up. make a query with only one join, test it. if it works, add a second join. test it. etc....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

What are the PrimaryKeys for the 2 tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Don't know if this makes a difference in JET or not, but I never have to specify the "fields", it's just:

DELETE FROM TABLENAME WHERE CONDITIONS

So, for this query I would expect:

DELETE FROM [Current Period Reporting Table] 
INNER JOIN [Deletion Table] ON ([Current Period Reporting Table].[Pharmacy Number]=[Deletion Table].[Stla Paid Claims_Pharmacy Number]) AND ([Current Period Reporting Table].[Date Filled]=[Deletion Table].[Stla Reversed Claims_Date Filled]) AND ([Current Period Reporting Table].[Paid Date]=[Deletion Table].[Stla Paid Claims_Paid Date]) AND ([Current Period Reporting Table].[RX Claim #]=[Deletion Table].[Stla Paid Claims_RX Claim #]) AND ([Current Period Reporting Table].[Claim Sequence #]=[Deletion Table].[Stla Paid Claims_Claim Sequence #]);

Leslie