[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access VBA query hangs but equivalent table works fine

Posted on 2007-10-03
Medium Priority
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
  • 2
  • 2
LVL 10

Assisted Solution

slamhound earned 240 total points
ID: 20010971
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

ID: 20024843
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

clarkscott earned 135 total points
ID: 20027092
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

slamhound earned 240 total points
ID: 20031644
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

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

830 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