ADO: Is my VBA now running too fast....causing Excel to stop responding?

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
Hi

I've built a spreadsheet where I've got a list of say 200,000 Customer Reference Numbers and I setup a loop like
For i = 1 to 200000
Range("CustomerID") = Range("ListofCustomers").cells(i)
Call ProcessCustomer
Next i

ProcessCustomer is the macro that does all the hardwork for the customer currently in Range("CustomerID"). There's a list of select queries specified in the spreadsheet and the macro loops through each of the select queries, opening the recordset and pasting the results into the spreadsheet. See https://www.mrexcel.com/forum/excel-questions/1093956-ado-wild-card-confusion.html if a more detailed description helps. After it has pasted in that data, in then copies down formulas to the match the number of rows of data.

The problem
------------
It was taking about 1.5 seconds per customer - which isn't too bad considering all it has to do. But that's about 83 hours................It all worked fine :)
BUT
I noticed that a lot of the records that were in some of the database tables were no longer required. So I deleted those records and compacted/repaired the database. Once that was done, things really sped up and it was now down to taking just roughly 0.5 seconds per customer. HOWEVER, Excel now crashes/stops responding after a random number of customers. Sometimes it does the first 2800 customers say before crashing, other times it only does the first 200 customers before crashing.


Is it possible that the ADO/DB/VBA are now trying to do things too quickly?


Thanks
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Random crashes when copy and pasting... it's likely that the clipboard is actually crashing and causing the error. Add some API code to open, empty and close the clipboard following each record set. Better yet, stop using copy and paste. Load all the data into an array then unload it. It won't crash and it will be much quicker...83 hours... yuck. HTH. Dave
 
Upvote 0
Re: "Stop using copy and paste"

Does this count as copy & paste?
Range(OutputRange).CopyFromRecordset RS


Also, a clue/pattern I've spotted is:
I've told the macro to save the file after every 1,000 "Customer Reference Numbers" have been put through all the process
It seems to always be during one of these Save operations that Excel stops working

Yet that didn't happen when everything was slower with the bigger database.

I've tried telling it to wait 10 seconds after the line that says ActiveWorkbook.Save but that hasn't solved it.
 
Upvote 0
Further to that question/description


When a CustomerReferenceNumber is put through the macro, it gets Access to perform 9 queries (some with sub-queries) and bring the results of the 9 queries into Excel

From googling it looks like myArray = rst.GetRows is a way of getting it into an array
Is really any better to be getting Excel/Access to do this 9 times using GetRows instead of CopyFromRecordset ?
(This is still a side question I think, because I reckon its something to do with saving that is causing Excel to stop working)
 
Upvote 0
I'm not familiar with using ADO recordset stuff but I assume that when your copying and pasting that your still using the clipboard. I think U will find that if U open each file and load it into an array and then only load the excel sheet once from the array then things will be much quicker without the crashes. I'll post a link that may be of interest. HTH. Dave
edit:https://www.mrexcel.com/forum/excel-questions/1077924-loop-through-formula-macro.html
This link just has code for both copy and array...
https://analysistabs.com/vba-code/range/m/copyfromrecordset/
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top