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.
LVL 4
Keyboard CowboyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Access has an internal limit of 2048 table references, which sounds like a lot, but each field reference is also a table reference.  

First, make sure all queries have only the fields required ( no. *).    If a report is based on a table,  change it to a query and include only the fields needed.    The sub reports are the main problem though.   You may find you have to flatten the data and do everything through the main report.

Jim

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keyboard CowboyAuthor Commented:
Thanks - I'm afraid I'm in over my head on this  -  there are 20 or so fields with nested queries, etc.  I may rewrite the whole area I guess though no idea where to start.  Thanks  for the info - very helpful
Dale FyeOwner, Developing Solutions LLCCommented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
Keyboard CowboyAuthor Commented:
Thanks - yeah, I'm all about backups and backups of backups sometimes.  
It's a pretty big mess this application.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.