Access VBA query hangs but equivalent table works fine

Posted on 2007-10-03
Last Modified: 2013-11-27
Access 2003 on WindowsXP:
(1) I ran the duplicates wizard to get Dups in a table on 2 fields, about 3,000 out of 170,000
(2) In a module I wrote a sub to open the DupQuery as a recordset, loop through it, build a SQL string to delete from the table the NumberOfDups -1
(3) The problem is it hangs halfway through requiring TaskMgr to abort
(4) I made the DupQuery a MakeTable query and ran it and it works fine.

Question: Why does the query cause it to hang, but the table based on it work fine?
Question by:Wicklunda
    LVL 10

    Assisted Solution

    My guess is some sort of infinate loop or unexpected data affecting your code.

    Use a debug.print strSQL to output your SQL statements and hit ctrl-G to make sure the results are on the screen when the system hangs. Then check out the data/record (and possibly the one after it) where it hung. You will very quickly be able to tell if you've got an infinate loop or if strange data is messing with yourr SQL statements.

    If it just hangs, add a counter to stop the process at 1500 records, then half that, then half that etc till you hit the record it's failing on.

    Author Comment

    It's not the data that is the problem, because it works fine from a table. There is something about the query causing Access to cycle more or something.
    LVL 20

    Accepted Solution

    Try putting some 'time delays' in the loop.  Sometimes the code works faster than the process and strange things occur.

    For J = 1 to 100000
    Next J  

    or something similar

    Scott C
    LVL 10

    Assisted Solution

    This could very well be the case but I'd like to see what your code is generating as SQL statements as it might not just be the data but a) how the data affects your code and b) how your code affects the data.

    Author Comment

    Thank you all for your help, but these did not really answer the question and I found a workaround.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now