My database sometimes produces Error 3048: Cannot open any more databases. After researching this on the Internet I now know that it is because Access has an internal limit of 255 simultaneous connections to Jet. A “connection” includes things like combo boxes, recordsets, queries, etc…..and these add up faster than one might think.
I believe the culprit may be a particularly complex report my users frequently run. It is composed of several subreports and each subreport is based on several nested queries.
I used nested queries a lot throughout my database. And sometimes these nested queries contain subqueries further increasing their complexity.
For example, the complex report probably requires at least 30 queries to run to produce the output. My questions are:
1) Are all 30 “query connections” open while the report is open? Or are only the final queries in the nested series open?
2) I could create temp tables and append the data to the table and then pull it out with a query and base the report on this. Would this reduce the number of connections because only the queries pulling the data from the tables would be “open”? (Generally, temp tables seem to be frowned upon by the experts on this forum)
Sometimes I do use recordsets and I have also gone back through to make sure that each time I open a recordset it gets closed e.g., rst.close.
Thanks.
I believe the culprit may be a particularly complex report my users frequently run. It is composed of several subreports and each subreport is based on several nested queries.
I used nested queries a lot throughout my database. And sometimes these nested queries contain subqueries further increasing their complexity.
For example, the complex report probably requires at least 30 queries to run to produce the output. My questions are:
1) Are all 30 “query connections” open while the report is open? Or are only the final queries in the nested series open?
2) I could create temp tables and append the data to the table and then pull it out with a query and base the report on this. Would this reduce the number of connections because only the queries pulling the data from the tables would be “open”? (Generally, temp tables seem to be frowned upon by the experts on this forum)
Sometimes I do use recordsets and I have also gone back through to make sure that each time I open a recordset it gets closed e.g., rst.close.
Thanks.