Delete Query Stopped Working

[Doug B]

I am trying to find out why a delete query that has worked well for months
has suddenly stopped working. I am getting the message "Could not delete
from specified tables.

Here is the SQL
DELETE [Beginning MEDICAL ONLY Inventory].*, [CURRENT ALL CLAIMS].[WC Claim
Type]
FROM [CURRENT ALL CLAIMS] INNER JOIN [Beginning MEDICAL ONLY Inventory] ON
[CURRENT ALL CLAIMS].[Claim Number] = [Beginning MEDICAL ONLY
Inventory].[Claim Number]
WHERE ((([CURRENT ALL CLAIMS].[WC Claim Type])="IN"));


When I look up the error in the help it says the database could be set to
read only but I am the original creator of the database and I am the only
user.

Any help would be greatly appreciated.

 

[Jerry Whittle]

Is the [Claim Number] field in at least one of the tables the primary key
field for that table? If so are these two tables linked in the Relationships
window with Referential integrity on? If not, you might have an "orphan"
record in one of the tables.

Also you may have a related record in a third table blocking the process.

Actually if you have the tables joined with RI on and the [CURRENT ALL
CLAIMS] is the parent table, you could enable Cascade Delete on that join in
the Relationships window and just this should delete the related records in
both tables:

DELETE [CURRENT ALL CLAIMS].*
FROM [CURRENT ALL CLAIMS]
WHERE [CURRENT ALL CLAIMS].[WC Claim Type]="IN";

 

[John Spencer MVP]

As far as I know that query should never have worked in Access. Access should
complain because it won't know which table to delete records from and it can
only delete records from one table at a time.

Actually, I can't tell either. To delete records from the table [Beginning
MEDICAL ONLY Inventory], Access would expect the SQL to look like the following:

DELETE
FROM [Beginning MEDICAL ONLY Inventory]
WHERE [Claim Number} IN
(SELECT [Claim Number]
FROM [CURRENT ALL CLAIMS]
WHERE [CURRENT ALL CLAIMS].[WC Claim Type]="IN")

You might be able to make it work with the following SQL

DELETE DISTINCTROW [Beginning MEDICAL ONLY Inventory].*
FROM [CURRENT ALL CLAIMS] INNER JOIN [Beginning MEDICAL ONLY Inventory] ON
[CURRENT ALL CLAIMS].[Claim Number] = [Beginning MEDICAL ONLY
Inventory].[Claim Number]
WHERE ((([CURRENT ALL CLAIMS].[WC Claim Type])="IN"));

Note the addition of DISTINCTROW and the removal in the first clause (DELETE)
of any reference to the Current All Claims table

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Doug B]

I made sure the claim # is the primary key on all my tables and it
worked...Thank you very much
--
db

Is the [Claim Number] field in at least one of the tables the primary key
field for that table? If so are these two tables linked in the Relationships
window with Referential integrity on? If not, you might have an "orphan"
record in one of the tables.

Also you may have a related record in a third table blocking the process.

Actually if you have the tables joined with RI on and the [CURRENT ALL
CLAIMS] is the parent table, you could enable Cascade Delete on that join in
the Relationships window and just this should delete the related records in
both tables:

DELETE [CURRENT ALL CLAIMS].*
FROM [CURRENT ALL CLAIMS]
WHERE [CURRENT ALL CLAIMS].[WC Claim Type]="IN";
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to find out why a delete query that has worked well for months
has suddenly stopped working. I am getting the message "Could not delete
from specified tables.

Here is the SQL
DELETE [Beginning MEDICAL ONLY Inventory].*, [CURRENT ALL CLAIMS].[WC Claim
Type]
FROM [CURRENT ALL CLAIMS] INNER JOIN [Beginning MEDICAL ONLY Inventory] ON
[CURRENT ALL CLAIMS].[Claim Number] = [Beginning MEDICAL ONLY
Inventory].[Claim Number]
WHERE ((([CURRENT ALL CLAIMS].[WC Claim Type])="IN"));


When I look up the error in the help it says the database could be set to
read only but I am the original creator of the database and I am the only
user.

Any help would be greatly appreciated.