Does background refresh of queries have an effect on macros? I've queried a lot, but so far only ran them with the command. However I'm developing a tool for multi-users who need to have access to it at any time. Originally I thought I'd refresh it once or twice a day when nobody is on it, but with the number of queries that is required, it takes too long. I wonder whether the refreshing would be problematic or get in the way and cause quirky things if it is always running in the background even when macros are running. (Frankly I've sort of been scared of using background refresh, I like to have control over what is happening in my projects all the time. Can someone tell me what I should keep in mind for using the background refresh?)
Thanks in advance.
Background refresh and macros compatible?
-
-
-
Hi socha
I would set the Refresh to run on Open. Perhaps add a line to the start or end of your code to run it as well?
-
Rather that using refresh maybe run the VBA script to ADD, you will need to clear the query to achieve this [totally clean REMOVE IT] so in effect you are running a new query for each refresh.
Or look at the refresh code line. What does it say? Might be good to post you code if possible
Kindest possible regards
Jack in the UK
-
Let me explain a little better. The query isn't in code. The query is one part of the document, and the macro is another, they aren't set up to work together. The document setting would have the query property to run in the background, so it is not in code. So I was wondering if the query running would interfere when the macros are set in motion.
-
I don't know the answer, but I would guess there would be no interference. Are the macros operating on the data the query returns? If so, does it matter what version ofthe data they work on? Why not try it for a few days on a copy and see if any problems develop?
-
Great idea, I'll test a dummy document for a few days. The data doesn't immediately to be the latest version, just so that the information is not getting too old. I'll let you know if it works out.
-
Hi socha
What I was suggesting was putting in one line of code to do the refresh just before the macro runs. This will ensure the refresh doesn't run while your macro is.
You don't state whether your macro is dependent on the data being the most up-to-date.
-
Dave, thanks for the suggestion. The queries take so long to run, so I don't want the users to have to wait for them to run. I'll try a test on the background refresh setting. The macros aren't dependent on the queried data being the most up-to-date.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!