"Cannot Open Any More Tables"... or can I?

[Pyro]

Hello!

So i am ploughing away through creating an application, and i get to building a report based on a query that references 5 or 6 tables. The query itself is not too complex, but does include aggregation and may pull criteria from several optional form fields.

If i base the Record Source of the report on the actual SQL that forms the query, i find that the report is sluggish to load (< 1 second, but still noticable). However, if i base the report on the same but seperate query, loading appears instantaneous. Furthermore, if i use SQL as the Record Source, then after about 20 or so times of opening the report, i get the error "Cannot Open Any More Tables". However, again if i base the report on the same but seperate query, i don't seem to have this issue (tested with 50 open and closes of the report).

My questions are twofold:

1) If anyone ever had this occur in a similar situation, what other work around did you come up with? And;
2) What is considered "best practise" for these scenarios. Should a form/report be based on SQL, where more than one table is required for the record source, or should it reference a query?

Thanks.

 

[DCrake]

You mention opening the report from a recordset. Are you closing the recordset after the report has run and then releasing it? such as:

Rs.Close
Set Rs = Nothing

By not doing so you are leaving multiple instances of the recordset open.

David

 

[namliam]

The difference between a stored query and a sql is that the sql is to be 'evaluated' time and again, while the stored query is 'evaluated' when you save it. Next time you open it without changing evaluation is not needed. This shouldnt be much, but with more and more joins can be noticable.

Dont know what is the best practice, I base my reports off queries. But that is rather cause I am more convertable with queries than reports and hidden queries as a recordsource.