Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Delete Query Error "Can't delete from specified tables

Discussion in 'Information Technology' started by Maureen227, Jul 28, 2006.

  1. Maureen227

    Maureen227
    Expand Collapse
    Guest

    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.
     
  2. Loading...


  3. Ron2006

    Ron2006
    Expand Collapse
    Guest

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

    Ron
     
  4. Maureen227

    Maureen227
    Expand Collapse
    Guest

    Ron2006 wrote:
    > 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
     
  5. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    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
     
  6. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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
     
  7. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Maureen227" <maureen227@cox.net> wrote in message
    news:1153914320.157937.117070@h48g2000cwc.googlegroups.com...
    > 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.
    >
     
  8. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Maureen227" <maureen227@cox.net> wrote in message
    news:1153914320.157937.117070@h48g2000cwc.googlegroups.com...
    > 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.
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     

Share This Page