Need Help with Refreshing Querytables.

[Accesshelp]

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.

 

[FSt1]

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

 

[Accesshelp]

FSt1,

Thanks for your response. I do not have any code for my querytables.

The querytables are created via Data>Import External Data>New Database Query.

Thanks.

 

[FSt1]

hi
sorry. i assumed that since you posted under programming that you had code.
how do you refresh the queries.

regards
FSt1

 

[Accesshelp]

The queries are refreshed automatically after each value changes. The
problem is when refresh is done, all the queries are refreshed before my
vlookup formula to do the calculation.

Thanks.

 

[FSt1]

hi
sorry to take so long. got hung up.
how have you got this set up to refresh automaticly. there has to be a
trigger.
i still think you problem is allowing a background query. you may have to
get away from the automatic thing and opt for a 1 refresh at a time.

Regards
FSt1

 

[Accesshelp]

Good morning FSt1,

Thanks for continuing to help. Part of the setup via Data>Import External
Data>New Database Query I can check the options to Enable Background Refresh
and Refresh Data On File Open from the External Data Range Properties window
and the option to Refresh Automatically When Cell Value Changes from the
Parameters window. If I have those options checked, the querytables will
refresh automatically after entering the values.

On the other hand, I did test to run the querytables without those options
and it did not work.

At this point, I don't know what to do.

Thanks.

 

[Accesshelp]

FSt1,

I am just writing to let you know that I write the code to do the refreshing
and calculating formula. I think it is impossible to use the automatic
refreshing feature based on the situation that I am in.

Thanks again for your help.

 

[AFSSkier]

Disable the "Enable Background Refresh" in the Connection Properties. This
does the same as FSt1's suggestion to "backgroundquery:=false", w/out any
code.

As long as you have the option to "Refresh Automatically When Cell Value
Changes" from the Parameters window. The querytables will refresh
automatically after entering the values.

Kevin