PDA

View Full Version : Recordset B takes longer to copy/paste to worksheet than Recordset A, but is smaller!



Vee Bee Hey!
11-12-2015, 07:59 AM
Hi folks,

New to the forum but used it several times over the last few months to solve problems I had encountered. However, I'm stumped by my current issue!

I have created an app in Excel which has a userform containing a number of comboboxes and textboxes users can complete which ultimately builds a SQL query to search for products. They don't need to use all fields, but they need to use at least 1. This SQL query then gets sent off, using an ADODB connection to an MS Access database I have created, queries a table, the resulting recordset is then copied and pasted into a worksheet using the CopyFromRecordset method and then the contents of the worksheet are used to populate a listbox (I created this nearly two years ago so my memory is a little hazy but I think I did it this way to give control over the size of the columns in the listbox as column sizes can vary depending on the results that are returned).

The problem I have is that you can search for a product using 1 field, and return, say 1500 records and take 2 seconds but then restrict the search further using an additional field - which obviously returns less records - say, 40 but the time taken to copy across the recordset can be substantially greater, e.g. 60 secs. In other words, smaller recordsets can take much longer to copy across.

Initially, I thought this was to do with the SQL query, but I have stepped through the code and it is definitely the CopyFromRecordset where the delay is.

Given that the connection parameters are the same and the number of fields in the recordset are the same for these queries, is there anything else that can affect the time taken to copy across?

Thanks in advance for any assistance you can provide!