Results 1 to 5 of 5
  1. #1
    megsull is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    5

    Delete Query Not Working - already set unique records to "Yes"

    When I try to run a delete query, I get the "Could not delete from specified tables" error. The query compares a table ("Pending Memberships") to a union query ("Current Members...") and aims to delete records from the table, not the union query. I imagine it's the union query that's screwing things up since the delete query used to compare one table to another, and since I threw the union query into the mix it's stopped working. Can a delete query not involve a union query at all? If no, is there a workaround? If so, can anyone figure out what's wrong with my current delete query? Code:
    Code:
    DELETE DISTINCTROW [Pending Memberships].*, [Current Members (Aggregate incl 60 day expired)].First_Name, [Current Members (Aggregate incl 60 day expired)].Last_Name, [Current Members (Aggregate incl 60 day expired)].Mailingadd1, [Current Members (Aggregate incl 60 day expired)].MailingState, [Current Members (Aggregate incl 60 day expired)].Expiration_Date
    FROM [Pending Memberships] INNER JOIN [Current Members (Aggregate incl 60 day expired)] ON ([Pending Memberships].MailingState = [Current Members (Aggregate incl 60 day expired)].MailingState) AND ([Pending Memberships].Mailingadd1 = [Current Members (Aggregate incl 60 day expired)].Mailingadd1) AND ([Pending Memberships].Last_Name = [Current  Members (Aggregate incl 60 day expired)].Last_Name) AND ([Pending Memberships].First_Name = [Current Members (Aggregate incl 60 day expired)].First_Name)
    WHERE ((([Current Members (Aggregate incl 60 day expired)].Expiration_Date) Like "*2013"));
    Thanks in advance for any help.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I have discovered myself that you cannot run an Update or Delete Query if any of the objects (tables/queries) used in the query are unupdateable. As you probably know, Union queries are not updateable. I find it rather annoying myself, especially when the field I am trying to run my Action on is in an updateable object!

    The workaround I have often done is to write the data out to a new temp table, and then use that in my Action query.

  3. #3
    megsull is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    5
    JoeM,

    Thanks for your reply. I had a sinking feeling that was the response I'd get. Is there any way you could give me a step-by-step of the workaround? I'm not really sure how to do that. Thanks so much.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I think all you need to do is the following:
    1. Create your query that has all the records you wish to delete.
    2. Change the query to a Make Table or Append Query to write those results to a new table (or pre-existing shell of a table).
    3. Use this new table in the place of your original query in the Delete Query to determine which records to delete.

  5. #5
    megsull is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    5
    That worked great! Thanks!

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

Similar Threads

  1. Replies: 11
    Last Post: 03-29-2012, 02:32 PM
  2. Replies: 2
    Last Post: 11-29-2011, 05:26 PM
  3. Replies: 11
    Last Post: 03-30-2011, 01:08 PM
  4. Replies: 4
    Last Post: 06-14-2010, 02:31 PM
  5. Replies: 21
    Last Post: 06-03-2009, 05:54 PM

Tags for this Thread

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