which way would you access data?

[no bosh]

I am looking for suggestions.
How would you suggest I pull this data into reports?


I created created a fairly elaborate (for me) set of queries in Access
reading FoxPro data.
There is a table created from all this in Access which varies
depending on the date ranges entered and depending on what periods are
being analyzed.

Anyway, I currently pull external data from this table into an Excel
sheet but have it autorefresh on opening. I am using this
autorefreshing sheets filtered data in lots of other worbooks.

I am doing this in the course of a macro running. It opens a sheet
which autorefreshes and then closes. I am not sure this is the best
way to do that. I am not sure enough time is elapsing if I do this in
code.

Perhaps I should try ADO, (which I am less familiar with) to connect
to the table directly.

Ultimately all this is to track sales performance, accounting for all
new invoicing by salesmen but then also tracking their credit returns
and adjusting.

So I am storing the data pulled from this table at certain points in
time and then tracking the progressive adjustments and finally
adjusting commissions based on original and final thresholds.

I value all the accumulated wisdom here so I am asking if the kludge
way, which works, could be done in a much simpler way by learning a
different way to get at the data table.

Thanks,
ScottD

 

[Bob Phillips]

Scott,

Is there a problem with the way you currently do it? If it works, then there
would appear to be no reason to change, unless you want to learn say ADO, or
you want to extend it and can't figure out how.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[K Dales]

I agree with Bob - and if you need to make sure the query is done refreshing,
there is the QueryTable.Refreshing property that lets you know if the table
is still refreshing. You could do a loop like this to pause your code until
the query is done - I will even include a time limit (10 minutes here) to
make it fail-safe:

InitTime = Now

While Sheets(SheetName).QueryTables(1).Refreshing and (Now <= InitTime +
TimeValue("00:10:00"))
DoEvents
Wend

If Sheets(SheetName).QueryTables(1).Refreshing Then
Sheets(SheetName).QueryTables(1).CancelRefresh
' Timed out, cancelled refresh - Error handler here?
End If

 

[Tom Ogilvy]

In the querytable properties, there should be a property for allow
background refresh I believe. This should be unchecked. I can't
positively say this will help, but I believe it will.

 

[no bosh]

Thank You all.
As you say if it works why fix it.
I will implement some of the waiting for refresh into my code though.
I appreciate the input

ScottD