Error 3048 Too Many Databases

Steve Gilbert
Steve Gilbert used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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?

Author

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 (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
Distinguished Expert 2018

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

Author

Commented:
Thanks for the link Daniel, very interesting - I'll see what I can apply to my application

Author

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 LambertConsulting
Distinguished Expert 2017

Commented:
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 (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<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.

Author

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
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
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.

Author

Commented:
Thank you all for your comments, it's helped me to clarify what I need to do to fix this

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial