Error 3048 Too Many Databases

Hi,
I have an MS Access database (2007-2013 format, .accdb) split into front & back end. It was developed on a Windows 10 PC and uses tabbed subforms. The live environment is  also on a Windows 10 PC with just 2 users, the second user on a separate PC with their own FE copied onto that PC when starting the database.
It seems to run fine on the development PC but on the live environment they are coming across the Error 3048 "too many databases" problem.
I've done a lot of research on this error and I understand the difficulties in pinning it down but I've made the following changes:
1) All DAO datasets are closed and the associated "set" commands are all cleared to "nothing"
2) I've replaced all "DLookUp" with Allen Browne's "ELookUp" routine
3) I've tried late binding on some of the tabs so that all recordsets are not populated when starting
The problem is still happening after making these changes & from what I've read it looks like I need to re-design the application but this is a major decision for the users to take & I'm wondering if there are any other things I could try as a stop-gap. I'm considering the following:
1) Forms that use queries as the RecordSource - would there be less overhead if I were to use SQL statements rather than queries?
2) Would a SQL back-end running on the local PC solve this problem?
I'd be grateful for any suggestions please.
Steve GilbertAsked:
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.

Dale FyeOwner, Developing Solutions LLCCommented:
do the users who are encountering this keep lots of tabs open simultaneously?

Do some of your forms have multiple subforms which are all open at the same time.

Do you have more indices on your tables than you really need?
Steve GilbertAuthor Commented:
Hi Dale,
Thanks for your reply. The main form contains 6 tabs which users press depending upon the situation. Some of the forms  on the tabs do have multiple subforms & this is where I've used late binding so that the datasets are not loaded until the data is required. I haven't checked the indices on the tables but will have a look at that. Thanks again for your help
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<1) Forms that use queries as the RecordSource - would there be less overhead if I were to use SQL statements rather than queries?>>

No

<<2) Would a SQL back-end running on the local PC solve this problem?>>

No.

 This error is not "too many tables" per say.

 Access has an internal limit of 2048 Table ID's and a table ID is used for every field that is currently active.  So in short, it's the complexity you have with forms.

 Are you using SELECT * FROM <table> in your queries?   That's a big culprit.

 and by the way, I've never found that having a lot of indices causing this.   It's simply the number of fields referenced at one time in all the open forms, reports, queries, etc.

 You are going to need to re-factor the app.

Jim.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Steve GilbertAuthor Commented:
Hi Jim, Thanks for your reply. I think I'm starting to get a bigger picture now & that I need to look at the number of fields & I'll also check the queries & SQL
Dale FyeOwner, Developing Solutions LLCCommented:
JIm,

I thought that the indexes also contributed to the table ID count.

I cannot say that I've run into this very often over the years, but in the cases I have, I do recall having to replace SELECT * with SELECT Fieldlist, and that pruning unnecessary indices also seemed to help.  But that may have simply been about reducing the number of columns selected, not about the indices.

Dale
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Have busy is the form and subforms?  When I see this issue it is usually due to to many connections (forms/subforms, combo boxes, listboxes, ...) and you need to lighten the load.  One way to do this is to use dynamic loading in which you only load tab content when the tab is actually clicked, you only load combo boxes when they receive focus, ...

Here is one article on the subject, Google and you can find more
https://www.databasejournal.com/features/msaccess/article.php/3599781/MSAccess-Load-Subforms-Dynamically.htm
Steve GilbertAuthor Commented:
Thanks for the link Daniel, very interesting - I'll see what I can apply to my application
Steve GilbertAuthor Commented:
OK, so having read through the article link supplied by Daniel (https://www.databasejournal.com/features/msaccess/article.php/3599781/MSAccess-Load-Subforms-Dynamically.htm) this seems to be suggesting loading the subforms as/when the relevant tab is clicked on by setting the sorceobject of the subform.
The basic structure of my data is as follow:
Level 1: Group Holidays
Level 2: Clients signed up for that Group Holiday
Level 3: Client Contact Details, Quotations/Bookings for Clients, Flight Info for Clients, Client Payments, Client Notes
So, when the user selects a Group Holiday the associated list of Clients is displayed and there are tabs for each of the the Level 3 categories.
At the moment I'm using "late binding" on some of the Level 3 subforms so that the recordsource is set up dynamically when the relevant tab is clicked but the article is suggesting setting the sourceobject when the tab is clicked. So my questions about this are:
1) is there any advantage to setting up the sourceobject rather than the recordsource?
2) Does this just happen once after the application is started so that when the next Group Holiday is selected the sourceobject or recordsource for the tabs is already set up (assuming they've been clicked previously)?
3) If it just happens once, could I clear the sourceobject or recordsource when a new Group Holiday is selected?
4) the article also refers to Master/Child Links for the subforms, suggesting that these could also be set up dynamically but I'm wondering whether it's necessary to set these up at all - they could be linked by setting up criteria in the subform query to select data associated with the parent form. Would that be a good approach?
Again, thanks for everyone's help, much appreciated
Fabrice LambertConsultingCommented:
Hi,

What do you mean by ""late binding" ?
To my knowledge, this is a VBA technic to prevent references nightmare, it has nothing to do with forms / subforms.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<What do you mean by ""late binding" ?>>

 The term applies to a lot of things and just means setting things at run time rather than at design time.

 Typically one binds a form or report to a record source at design time.  But if you do it at run time, you don't get hit on the table ID's until you do.

 Personally I don't like that approach.   If I'm that close to the edge, I'd refactor the interface and then you don't have to jump through hoops when working on the app.

Jim.
Steve GilbertAuthor Commented:
Jim - Thanks for your comments - I have to agree: maintaining the app becomes a bit of a nightmare. So I think I'll just have to bite the bullet & do some re-design to cut down on the amount of data being loaded
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I would look at your tabs and see if there is anything you could do as a pop-up form to display details.  That would be a quick way out of this.

 Also, don't forget to look at the SQL and pull in only the fields you need.

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
Steve GilbertAuthor Commented:
Thank you all for your comments, it's helped me to clarify what I need to do to fix this
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.