Access is the best report designer on the market. It is a real shame about this .adp report problem. Access does not release memory it uses to generate reports, the memory only gets released when access is shut down, the memory used is not even reused on subsequent reports which then means the memory usage is cumulative until access crashes.
Note: You can close Access prior to the crash and the memory is released, however, this is not an option for some clients
So, what can you do about this problem?
Creating reports against a large number of records using .adps is never a good idea. In a Client/Server world, the trick is to request the smallest number of records needed for your application. In the Jet world, this was seldom a problem because Access relied on using the Jet engine as the "cursor" engine for performing the grouping and sorting operations and it did so using temp table structures. The SQL Server engine wasn't designed for this type of "server-side" cursor notion so the only way for an application like Access to do the work is to cache the results. Now, it turns out that Access uses the Windows Cursor Engine and the Shape Provider - components that are part of the MDAC stack for caching the data and their are known issues with memory not being released by them - the Shape Provider in particular.
Here are three suggestions to solve the problem...
1) ADPs - Consider structuring the report in such a way that you limit the number of records going to Access. One way is through the use of Views, Functions, or Stored Procedures to pre-process the data before giving it to Access. This means that you would use SQL Server for computing calculated column values rather than doing it in the Access report designer.
[Suggestion to Microsoft: Why did you use Memory (which includes the page file), instead of using Temp files….. Temp files are going to be faster and more scalable on big reports.]
2) MDBs - move the reports to .mdbs and leave the forms in the .adps. When the report is called it shells out to another instance of Access….. (sounds like this may cause more problems). Consider when using the .mdbs, you import the data into Jet first and then run the report off the mdb. The idea is to import the data into Jet that needs to be processed; design your report against the appropriate tables; then delete the data from these tables before each run of the report. By loading the data directly into Jet, you can often achieve dramatic performance increases because of the way Access can use Jet to directly cache the data.
Bottom line: This is one of the many reasons why there is still a need for Jet.
Both these methods are classic examples of using "server-side" cursors to optimize performance for outputting reports.
3) Check Memory - If all you want is Access not to die after 100’s of reports are opened how about having a function in the .adp that looks at the memory used and when it is getting to a critical stage (before Access dies), it gives a warning. Eg. You could have a msgbox with “Warning, memory is very low. Please close Access and re-open.” This could be called every time a form or report is opened…. (If anyone is in a position to give me the code I would appreciate it)…