Fredd
asked on
Cannot open any more databases and reports/subreports
I'm working with a legacy database and associated VBA code. I first it split it out to have a backend and front end (FE). The FE only has reports, queries, a very few tables, and forms.
I'm getting the beloved "cannot open any more databases" when attempting to print a report which has several (7) subreports.
Of course, this is with a database with linked tables but not that many (about 15).
I've already replaced DLOOKUP statements with ELOOKUP but didn't solve the problem. WIll replacing these subreports with logic in as a standard report help? In another situtation, I split the report into 3 different reports but the users are rejecting that as an solution. I'm on Access 2010 and 2013 (must run on both). I'm new at this so any advice tuned for the new guy would be most appreciated.
I'm getting the beloved "cannot open any more databases" when attempting to print a report which has several (7) subreports.
Of course, this is with a database with linked tables but not that many (about 15).
I've already replaced DLOOKUP statements with ELOOKUP but didn't solve the problem. WIll replacing these subreports with logic in as a standard report help? In another situtation, I split the report into 3 different reports but the users are rejecting that as an solution. I'm on Access 2010 and 2013 (must run on both). I'm new at this so any advice tuned for the new guy would be most appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start with each of the sub-reports. Open the report in design view, then open the RecordSet which supports that report. If the Recordset is a table, chances are that there are fields in the table that are not used in the report, create a query from that table and only include the fields necessary for that sub-report. You will know if you are missing a required field in the query because there will be a little green flag on the control if the control source does not exist in the underlying query. Save the query, test the report, if it doesn't work, fix the query until it does.
Then do the same with each of the other sub-reports and with the main report.
Then do the same with each of the other sub-reports and with the main report.
Also try to un-nest the queries if possible. The nested queries may contain a lot of fields you don't need for the report.
If your not sure where else they might be used, make a copy of all of them, then modify the copy for the report.
Keep in mind though that each query will need to be changed to point to the new copies (say A was referring to B, which you make a copy of called B_Copy - A_Copy now needs to refer to B_Copy).
Make a backup before you make any changes, then take it one step at a time and test as you go along.
Jim.
If your not sure where else they might be used, make a copy of all of them, then modify the copy for the report.
Keep in mind though that each query will need to be changed to point to the new copies (say A was referring to B, which you make a copy of called B_Copy - A_Copy now needs to refer to B_Copy).
Make a backup before you make any changes, then take it one step at a time and test as you go along.
Jim.
ASKER
Thanks - yeah, I'm all about backups and backups of backups sometimes.
It's a pretty big mess this application.
It's a pretty big mess this application.
ASKER