Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete query not responding (2000)

    I have the following in a delete query:
    DELETE tblSF.SFClient_Number
    FROM tblSF
    WHERE (((tblSF.SFClient_Number) Not In (Select Client_Number FROM Total_qry3)));

    It keeps running and when I go to cancel it - (ctrl-alt-delete) it says Access not responding.
    I ran a query to find the records in tblSF that do not match Total_qry3 (because these are the records I want to ultimately delete) and that ran fast and while it is running If I check to see if Access is responding - Access is responding.

    Is my delete query wrong or does a delete query take forever to run? After 15 minutes is when I cancelled it. I want to delete the whole record(s) if there isn't a match in total_qry3. Hope I am making sense. Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query not responding (2000)

    Would it be possible in this situation to replace Total_qry3 with the name of the table imported from Excel?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query not responding (2000)

    I think that would be OK - I will try that.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query not responding (2000)

    If so, you can also try this (always experiment on a copy of the database!):

    DELETE tblSF.*
    FROM tblSF LEFT JOIN tblExcel ON tblSF.SFClient_Number = tblExcel.Client_Number
    WHERE tblExcel.Client_Number Is Null

    tblExcel must be replaced with the name of the imported table.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query not responding (2000)

    Now I get Error 3086 - Could Not delete from specified table.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query not responding (2000)

    Do you happen to have the table open in design view?
    Does the error persist if you close then reopen the database?

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query not responding (2000)

    No - the table is closed. I closed the database and came back in but I still get the error.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query not responding (2000)

    Have you tried the original SQL (with the Not In construction) with the name of the table instead of Total_qry3 ?

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query not responding (2000)

    Yes, I have tried the original SQL with the name of the tbl. It runs and Access is responding but I haven't let it run to finish. I cancelled it after 8 minutes and tried the new code you sent. I will let it run and see how long it takes. Maybe it just takes a long time. Where is says "Run Query" at the bottom of the screen, I only get one dot and it doesn't go any further. I will see if it just keeps running or it eventually ends.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query not responding (2000)

    How many records does tblSF have, and how many the imported table?

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query not responding (2000)

    tblSF has 34020 and tblAllActive has 71441

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query not responding (2000)

    The query will take a LONG time then - processing Not In (...) is notoriously slow, and your tables are quite large.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query not responding (2000)

    I run a query to see what records are in tblSF that are not in Total_qry3. Is there any way to get the results (Client Numbers) from this query into a delete query that is setup to delete from tblSF where the Client Number field equals those client Numbers? I am probably asking something that isn't possible. It would probably be faster for me just to run the query to get a list of the client numbers that are not in Total_qry3 and than manually put them in the delete query and delete them that way.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query not responding (2000)

    You can change the query that returns records in tblSF without a match inTotal_qry3 into a make-table query (through the Query menu).
    You should then be able to create a delete query based on tblSF and the table created by this query, and this should run MUCH faster.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query not responding (2000)

    I made a table and then I made a delete query using that table with tblSF. I used the same code you sent but changed it to In(Select SFClient_Number From tblNotMatch. It takes about 3 minutes to make the table and run the delete query. I put both queries in a macro - it works and it is fast. Thanks for your help.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •