Avatar of Steve Gilbert
Steve Gilbert
 asked on

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.
DatabasesMicrosoft AccessWindows OS

Avatar of undefined
Last Comment
Steve Gilbert

8/22/2022 - Mon
Dale Fye

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 Gilbert

ASKER
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)

<<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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Steve Gilbert

ASKER
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 Fye

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 Pineault

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve Gilbert

ASKER
Thanks for the link Daniel, very interesting - I'll see what I can apply to my application
Steve Gilbert

ASKER
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 Lambert

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jim Dettman (EE MVE)

<<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 Gilbert

ASKER
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
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Steve Gilbert

ASKER
Thank you all for your comments, it's helped me to clarify what I need to do to fix this
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.