Excel queries, named range and slow performance problems...

[LKACT]

Ok, so basically I have an excel workbook as front end and an access database as back end. I also have a sheet with cells storing information like the table names and access field names.
Codes to get information from these cells into an array and building SQL statements for queries works fine.
5 SQL statements were built and therefore I made five loops to retrieve data from different tables of the database for each query.

For each loop I need to get specific field data from the temporary recordset into some specific named ranges in various cells (yes, I have no choice to just copy the whole **** thing and paste it all to a range using copyfromrecordset!),

So basically I have another loop inside for each query loop to copy the data from the temp recordset to each named range.
Problem is, SPEED! I found that it may be caused by the part where I need to copy data by data to each named range (there are around 100!), but another major part could be caused by the number of queries.

I am not sure if 5 queries could cause such performance issue or it is still about the named range problem. So what I would like to ask is that if I join all 5 queries together and make only 1 query to get all the data, will it speed up the process in getting the information from Access?
and for the named range problem, it seems there are no workaround for this as there are like 100 named range in various locations, so the only option I have is to make loop and assign data to each named range cell one by one...
any help would be welcomed...

 

[Fazza]

Hi,

I expect one larger query to be faster than five separate queries pulling the same data. (One common source of reading delays, btw, can be if the source files are 'buried deeply' on network drives.)

For quickly loading the data into ranges, I'm not clear on what is required.

Some thoughts. Plain old query tables might be a good alternative. Use VBA as requried to set the connection, the SQL and refresh them. If given defined names beforehand, these will automatically adjust upon refresh. And adjacent formulas can be set to fill. Other ranges - not in the queries - can then simply link or run off formulas. It can be good.

Or, staying with recordsets. Likely the problem is writing to the worksheet. Cell by cell is slow & best avoided. Instead, paste all at once (for each named range). Such as, by loading the data into an array and then loading that to the worksheet. Or, and this is less likely, depending on what you are doing, work within ADO to filter the recordset or whatever else so that the paste can be done in one operation. It might be obvious, but still I'll mention, application.screenupdating = false and also setting calculation to manual should be used.

HTH. Regards, F

 

[SamTYler]

With only ~100 values, you can use columns or rows for this, but I'll use columns 1 and 2 for this untested idea.

On a hidden sheet, in column "A", paste the list of range names. In column "B", paste the copyfromrecordset.

Rearrange the names to match the record fields.
in VBA

For Rw 1 to ~100
Range(Cells(Rw,1).Value).Value = Cells(Rw, 2).Value
Next Rw