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

[PTP86]

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

 

[NdNoviceHlp]

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

 

[PTP86]

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.

 

[PTP86]

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)

 

[NdNoviceHlp]

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/