Results 1 to 14 of 14
  1. #1
    MooseOTL is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    2

    Trying to Delete record using delete query


    I have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"

    I think my problem has something to do with security but I can't figure out what to change.

    Thanks for any help.

    moose

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,435
    Can you show us what you tried?
    Did you get a specific error message?

  3. #3
    MooseOTL is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    2
    Quote Originally Posted by orange View Post
    Did you get a specific error message?
    the message I get is "Could not delete from specified tables"

  4. #4
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Hi and welcome to the Forum,
    There are reasons that why you cant delete records from tables.
    One reason is that, if your table has one to many relationships and the data integrity is set to cascade not to delete the related record in the related table, will not allowed you to delete the record until you also delete the related from the other table.

    Hope this help,

  5. #5
    Baldeagle is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136

    Delete query problem

    Hi. This thread seems to be similar to a problem I have so I hope you can help.
    At Turn of Year I have a query (as part of a large Macro) which identifies donors who haven't made a donation in any of the last 5 years. The SQL statement is
    DELETE Donors.*, [Donors by Year].[Donor Number], Donors.[Donor Number], [Donors by Year].Surname, [Donors by Year].[Christian Names]
    FROM Donors LEFT JOIN [Donors by Year] ON Donors.[Donor Number] = [Donors by Year].[Donor Number]
    WHERE ((([Donors by Year].[Donor Number]) Is Null) AND ((Donors.[Donor Number])>0) AND (([Donors by Year].Surname) Is Null) AND (([Donors by Year].[Christian Names]) Is Null));
    The query should delete these donors from my Donors table. This has worked in the past but for some reason it didn't work last time. When I tested it I am getting the message 'Could not delete from specified tables'. I don't understand why this is coming up - I am the designer of the database and the administrator so I can't figure out why it is blocking the carrying out of the action. The entries to be deleted are identified OK when I look at the output from the query.
    Can somebody solve this mystery for me please?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,435
    The Delete query only deletes records and only from one table.

    See http://www.techonthenet.com/sql/delete.php for syntax.

  7. #7
    Baldeagle is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    I am only trying to delete records from the Donors table on the basis of names not in the Donors by Year table. I have tried the following (on the basis of the information in the techonthenet website but am getting a syntax error message. Can you see what I am doing wrong?
    DELETE FROM Donors
    WHERE NOT EXISTS
    (Select '
    FROM [Donors by Year].*
    WHERE Donors.[Donor Number] = [Donors by Year].{Donor Number]
    AND Donors.Surname = [Donors by Year].Surname
    AND Donors.[Christian Names] = [Donors by Year].[Christian Names]);

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,435
    Untested, but edited,

    DELETE FROM Donors
    WHERE NOT EXISTS
    (Select *
    FROM [Donors by Year]
    WHERE Donors.[Donor Number] = [Donors by Year].[Donor Number]
    AND Donors.Surname = [Donors by Year].Surname
    AND Donors.[Christian Names] = [Donors by Year].[Christian Names]);
    Be aware that with a delete query, the change is permanent -- No UnDO.

    If the data is important, make a backup before doing a DELETE Query.
    Good luck.
    Last edited by orange; 10-04-2011 at 07:15 AM.

  9. #9
    Baldeagle is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Thanks for the correction, Orange. I have now been able to incorporate this into my Macro and have tested it. It now produces the right outcome. As you will probably have appreciated, I have no knowledge of SQL - I rely simply on the 'normal' design facility in Access. But this has been sufficient to enable to to produce quite a sophisticated database.
    Thanks again for your help. Sorry I didn't get back to you yesterday - but Sunday was my day of rest.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,435
    Glad you have it all working.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,663
    Baldeagle, your problem is solved, but you hi-jacked the thread started by MooseOTL. I realize the posts are months ago, but it will be hard to find your posts hidden under MooseOTL's original post.

    If you see a thread that is similar to your problem, start a new thread and refer to the other thread in your post.
    Last edited by ssanfu; 10-03-2011 at 07:40 AM. Reason: relevance
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,435
    Good point ssanfu,hijackin isn't good --- but MooseOTL hasn't been here since mid May.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,663
    Quote Originally Posted by orange View Post
    Good point ssanfu,hijackin isn't good --- but MooseOTL hasn't been here since mid May.
    True........ but if he found another thread that someone posted two days ago andhi-jacked that thread instead of starting his own..... its just not good netiquette. Just wanted to point it out.....

    Plus, there wouldn't be as many people reading his thread.......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #14
    Baldeagle is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Location
    Northern Ireland
    Posts
    136
    Sorry - I didn't understand the rules.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 03-30-2011, 01:08 PM
  2. Delete Record; Yes/No
    By mar_t in forum Access
    Replies: 5
    Last Post: 03-10-2011, 07:15 AM
  3. Delete record function
    By Evgeny in forum Programming
    Replies: 6
    Last Post: 04-12-2010, 09:19 PM
  4. Delete record if duplicate
    By rubintheyid in forum Access
    Replies: 8
    Last Post: 03-30-2010, 11:33 AM
  5. Delete a record
    By f.crocco in forum Programming
    Replies: 1
    Last Post: 10-08-2007, 07:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums