I have a workbook that pulls around 500,000 rows from an SQL database into a Connection Only table [SQLQuery] using VBA
is this correct
as my query is just called SQLQuery.
if I use this
I get an error when I attempt to run the VBA at this line.
I am then linking around 15 queries to this Connection Only table in the hope that the SQL is called only once.
However that is not happening. Every query is calling the 500,000 rows again and again from SQL causing a massive time lag.
I came across Table.Buffer which I think downloads all the SQL data into memory but I appear to run out of memory in my 32bit version of 365 office that the company favours over 64bit.
I have also read that PowerQuery works differently to PowerBI which has this issue solved.
I tried downloading the data set to a table but again this makes the entire workbook to large for a 32bit version of Excel
Do I need to resign myself to a 1 hour refresh time or can anyone suggest further tweaks to make this run quicker.
So far I have disabled Background Data in Query Options\Data Load
is this correct
VBA Code:
ActiveWorkbook.Connections("Query - SQLQuery").Refreshif I use this
VBA Code:
ActiveWorkbook.Connections("SQLQuery").RefreshI am then linking around 15 queries to this Connection Only table in the hope that the SQL is called only once.
However that is not happening. Every query is calling the 500,000 rows again and again from SQL causing a massive time lag.
I came across Table.Buffer which I think downloads all the SQL data into memory but I appear to run out of memory in my 32bit version of 365 office that the company favours over 64bit.
I have also read that PowerQuery works differently to PowerBI which has this issue solved.
I tried downloading the data set to a table but again this makes the entire workbook to large for a 32bit version of Excel
Do I need to resign myself to a 1 hour refresh time or can anyone suggest further tweaks to make this run quicker.
So far I have disabled Background Data in Query Options\Data Load
Last edited:
