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.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  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.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  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