Does MS Access leave objects open?

Question

I'm trying to figure out how MS Access is handling open database objects when I build and run reports.

I have dozens of queries in each report and, since my database is normalized, each query is looking at many different tables. However, I always seem to reach the point where I have so many database objects open, that Access won't let me include any more queries in the report. My idea of how the reports are being generated is probably naive, but I just assumed Access would run a query, place the data in the reports, close the query and all associated objects, and repeat. But this doesn't seem to be the case.

The only reason I even care is because I am trying to speed up my reports. I read somewhere that you shouldn't build your tables to make querying easy, but in an effort to avoid the 7 or 8 minute time-void that happens every time I try to run a report, I have re-constructed the tables so the queries can use less objects when generating the data. The tables are still normalized to an extent, but I guess the only down-side is I am technically storing calculations in the table. For example, I have to report the current age of our customer base, and instead of grouping them with a simple switch statement (which requires me to query a separate table that holds the birthday), I have placed the age ranges (18-22, 23-27, etc) directly in the table.

Anyways, if someone could enlighten me about how access is closing the database objects, maybe I can pinpoint why my reports are so slow.

Thanks

When you say Access won't let you include any more queries in the report, are you referring to the RecordSource of the report itself? On average, how many queries are you binding to a given report? — Tim Lentine
Google "JETSHOWPLAN". Seeing the quasi-query plans Jet/ACE is producing might help de-mystify things for you. It is especially helpful at identifying fields that should be indexed. — mwolfe02
Not to the record source itself, but via sub-reports. Combined, the sub-reports use about 80 queries (around 110 if you count the sub-queries) — jerry
"dozens of queries in each report" ? Can you give a sample ? I am building Access (some complex) apps since Access exists, and NEVER needed that. — Patrick Honorez
You say your tables are normalized. How can you have the age of your customers in a table then ??? By definition, this violates the essential rules of normalisation. — Patrick Honorez

Alex Alex · Accepted Answer · 2012-02-03T13:29:53

Supposing that you really have (correct) normalised tables, and the tables are really impossible to reduce, and you really need to append all those subreports in your main report, you should create temporary tables to set as your report's DataSource. It will be a little bit boring, but you can create a step where your queries are executed and generates one or more temporary tables. When these tables is created with the filtered data which you desire in the report, you should open the report much more quickly than before. After the report's exhibition/print you should drop the temp tables or mantain them just until you need those specific data, like a buffer.

Otherwise, I suggest you to consider the entyre design of your DB, if it is possible.

Does MS Access leave objects open?

1 Answers