Delete Query Error "Can't delete from specified tables

[Maureen227]

My Delete query uses only one table to delete from based on the query
result from another query using a different table.

My first query, get distinct values from an Item list, looks like this
and works great:
SELECT DISTINCT [JobLineItems].[ModelNum]
FROM JobLineItems;

Using that I identify the records in the products list that I want to
delete. This works also.
SELECT ProductsList.MFGName, ProductsList.ModelNum,
ProductsList.Description, ProductsList.ItemCost,
ProductsList.ItemRetail, ProductsList.DEPrice
FROM ProductsList, [A ModelNumQuery]
WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));

But, then I turn this query into a delete query. I get the error
message Cant not delete from specified tables.
DELETE ProductsList.*, ProductsList.ModelNum
FROM ProductsList
WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));

If I put a model number in the query it works. Ex, WHERE
(((ProductsList.ModelNum)=[ ="AMP-100"

The Products list has ModelNum as its primary key and is not related to
any other tables. IT IS USED FOR LOOKUP.
I have tried it with DISTINCTROW and without. No good.

Any help would be appreciated.

 

[Ron2006]

My only experience with that error message is that I get the message
when there are NO records to delete.

Ron

 

[Maureen227]

My only experience with that error message is that I get the message
when there are NO records to delete.

Ron

Thanks for the r€eply Ron. I checked and the records I want to
delete are there. i think I am going crazy.

Maureen

 

[Nikos Yannacopoulos]

Actually, the problem here is that the query involves two tables, in
such a way that the resulting recordset is not updatable. look up
"updatable recordset" in Access help for more on the subject.

Solution to the problem at hand provided in the queries NG where the
question is also posted.

Nikos

 

[Ron2006]

I thank you also...

Now I know another way I can get the error.

(At least this is better than one error message I got when working on
an IBM360 ISAM DB. The message literally was: "None of the Above". We
used that system from 1977 thru 1984 and I got it twice. And right now
I can't remember what the final cause was.)

Ron

 

[Pieter Wijnen]

You can't delete from a query with distinct clause in the SELECT Or FROM
part

However this should work

DELETE FROM PRODUCTLIST A
WHERE EXISTS (SELECT 'X' FROM JobLineItems B WHERE B.ModelNum = A.ModelNum)

HTH

Pieter

PS Please retain the whole thread when answering threads, so we don't have
to go all the way to the top to see the original question

 

[Pieter Wijnen]

You can't delete from a query with distinct clause in the SELECT Or FROM
part

However this should work

DELETE FROM PRODUCTLIST A
WHERE EXISTS (SELECT 'X' FROM JobLineItems B WHERE B.ModelNum = A.ModelNum)

HTH

Pieter

PS Please retain the whole thread when answering threads, so we don't have
to go all the way to the top to see the original question

My Delete query uses only one table to delete from based on the query
result from another query using a different table.

My first query, get distinct values from an Item list, looks like this
and works great:
SELECT DISTINCT [JobLineItems].[ModelNum]
FROM JobLineItems;

Using that I identify the records in the products list that I want to
delete. This works also.
SELECT ProductsList.MFGName, ProductsList.ModelNum,
ProductsList.Description, ProductsList.ItemCost,
ProductsList.ItemRetail, ProductsList.DEPrice
FROM ProductsList, [A ModelNumQuery]
WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));

But, then I turn this query into a delete query. I get the error
message Cant not delete from specified tables.
DELETE ProductsList.*, ProductsList.ModelNum
FROM ProductsList
WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));

If I put a model number in the query it works. Ex, WHERE
(((ProductsList.ModelNum)=[ ="AMP-100"

The Products list has ModelNum as its primary key and is not related to
any other tables. IT IS USED FOR LOOKUP.
I have tried it with DISTINCTROW and without. No good.

Any help would be appreciated.

--