Multiple Reports – Same Query – Underlying Data Changes Frequently

[Brad]

We have multiple reports that use data from a single query. This query is
tied to another application’s tables via ODBC.

The data in the other system’s tables is being updated continuously.

Currently we run each Access 2007 report separately which in turns initiates
the underlying query for each report.

This works fine, but we are concerned that we may see different results in
the case where the data in the underlying table is changed between when
Report1 is run and when Report2 is run. (It is important that the summary
data is the same on both reports)

Is there a way to force Report2 to use the results of the query that were
built when Report1 was run?

We have started to experiment with sub-reports, by making Report2 a
sub-report of Report1. However, It appears that the underlying query is run
once for the main report and run again for the sub-report.

Thanks in advance for your help.

Brad~~~~~

 

[John Spencer]

If there is no way to filter the data from the linked ODBC data so it is
consistent - for instance a datetime field that records when the record is
entered/updated - you will need to populate a local table with the needed data
from the ODBC linked table and then run the report(s) using the local table.

I have some reports where I use a temporary database and temporary table in
that database.

See Tony Toews' website
http://www.granite.ab.ca/access/temptables.htm
for an example of how to create a temporary table in a temporary database

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Brad]

John,

Thanks for your help. I do not think that there is a field that we can use
for a filter in the linked ODBC data. I like your suggestion of using a
temporary table.

I was hoping that there might be a way in VBA to run a query and then run
multiple reports based on this query, without running it again. It sounds
like this is not possible.

Thanks,
Brad

 

[Brad]

I posted this question two times. Sorry about that. I checked my first post
after several hours and it was not showing up, so I posted it a second time.
When I have posted questions in the past, it only took a few minutes for them
to show up. I am not sure what is going on.