Data Connection: "Enable Background Refresh" versus "Enable Fast Data Load"

[Isaac]

According to my research: 'enable background refresh' will allow the data connection to refresh in the background, i.e., you don't have to wait to use Excel in the meantime - you can fully use Excel while it finishes loading. 'enable fast data load' does the exact same thing in reverse....it causes the data load to be faster, but single-threaded (you can't use excel while it's loading, excel is frozen up).

Why are there 2 options, and what is the impact of having both checked? Which would seem to sort of conflict with each other?

I have tried to experiment and find out, but I'd also like to see if anyone has a direct knowledge/experience on these 2 properties and/or could explain to me why Microsoft has provided both of them, rather than just one.

 

[NauticalGent]

No idea, but if you are able to make a definite determination, please share!

 

[The_Doc_Man]

When you look up Excel's options, that "enable background refresh" allows EXTERNALLY bound data refreshes to occur as a background task. If you don't have externally bound data (like cells loaded by referencing another sheet in another workbook, e.g.) then this may have little effect on what you are doing. If your Excel datasheet includes VBA code to activate a query, this allows the query to run in background mode. Depending on the source of the query data, this could represent a long delay.

If you have external data and want it all to be loaded as quickly as possible, you allow Excel to run that query as a foreground operation. If so, Excel is single-threaded (just like Access is single-threaded). Which means it is busy during "fast data load." Again, this only really has a major effect if you are dealing with external data. If you have a self-sufficient or stand-alone sheet, the difference with or without the option should be pretty small.

The difference is therefore fairly simple. If you do "background" work, the child process has limits on resources it can grab. I.e. it is throttled by the fact of being a child process that must share resources with its parent process (Excel.EXE). If you allow the "foreground" operation then Excel's main task uses all the resources it can get from Windows - which includes "hogging" the CPU resources.

 

[Isaac]

I agree with you Doc, but here's my thinking/question:

Option 1: refreshes in background, you can use excel while refreshing, supposedly slower
Option 2: refreshes in foreground, you can't use excel while refreshing, supposedly faster

If you were designing this form for Microsoft, wouldn't you have made these two options mutually exclusive ?

It seemed logically odd to me that one can check both at the same time, which then led me to suspect "maybe there's something I'm missing here, that if I understood it, would cause me to think Yes that makes sense to be able to check both at the same time".

Just checked, and if I check both options, Excel seems to do Background style and ignore the other one.