cant' open any more databases

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

cant' open any more databases

Post by petern »

I've had a discussion with Hans about this previously on the other Lounge. This is sort of a followup.

I have a very complex report that is generating this error in Access 2007 but not earlier versions. I thought I had solved the problem by splitting it in two. Right now, I have it set so that each page is a separate report but both of them are being opened at the same time in Preview mode. Clearly this isn't good enough. So here's the question of the day:

If I set up these reports to be sent in sequence directly to the printer will this avoid the problem since one report is completely done before the second prints?

Thanks
Peter N

User avatar
HansV
Administrator
Posts: 74162
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: cant' open any more databases

Post by HansV »

I suppose that should work, but the only way of finding out is to actually try it.
Regards,
Hans

DollyP
Lounger
Posts: 46
Joined: 08 Feb 2010, 08:50

Re: cant' open any more databases

Post by DollyP »

Would it not be possible to set up an unbound report and embed your 2 reports as subreports?

David

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: cant' open any more databases

Post by petern »

The problem is that this is an unbound report with several reports as subreports each of which uses between 3 and 6 queries. The rather misleading error message about opening databases has to do with the number of connections to the database engine that are open at any given time for which there is a fixed maximum. This report reaches that maximum and, I suspect, even though I have split it into two, because I have them open in print preview the Jet engine isn't done with them and I'm still hitting the ceiling.
Peter N

User avatar
Charlotte
Her Majesty
Posts: 499
Joined: 19 Jan 2010, 07:13

Re: cant' open any more databases

Post by Charlotte »

There are any number of situations that cause "database connections" and it isn't simply a count of queries. Reports are particularly problematic in this regard because they create a "shadow" query when they run so you get two for the price of one! Also, if you have subqueries and nested queries, each of those counts too. In order to preview the report, all the subreports have to load, so the unbound parent report isn't actually saving you a connection.

One way of minimizing the problem is to turn off the subdatasheets in the database, and remove any table level stuff like combobox dropdowns, etc. When you drop those fields onto a report they carry extra baggage with them.
Charlotte

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: cant' open any more databases

Post by petern »

Thanks for the additional information, Charlotte. This particular report has been around for a long time and worked fine. When I did a new version for a different client that required even more queries for filtering purposes (multiple sites instead of one and two different government funders instead of one) is when the problem started cropping up. The easiest and cheapest solution, if not the most elegant, will be breaking the report into small enough chunks that I go back under the limit. The alternative is a lot a redesigning that I don't want to get into. Kind of like the big banks and insurance companies here in Canada that still use mainframes and COBOL programmers for certain backend stuff because they have way too much invested in them.
Peter N