Cannot Open any more databases

[NeilClark]

I have built an Access database that is throwing up error code 3048; "Cannot open any more databases". It has been doing it for a couple of years. I am using MS 365 on Win-10 Pro and it is all up to date. The system is totally hand built, using VBA, rather than macros. No templates were involved.

There is a support case with Microsoft on this, but it was not resolved there. Support Case: 25328611

I have done a full compact and repair many times and have even rebuilt the database by opening a new one and importing all the forms, queries, tables, etc. It is a split database that multiple people use, but the problem comes regardless of how many people are on the system - even just one person.

I have also increased the MaxLocksPerFile in the registry of the PCs using this database but this did not stop the problem.

I read that not closing record-sets and other objects could cause this problem, so I went through all of the code and added a number of closing statements that were missing, but this did not handle it.

The system has a number of forms that need to be opened and stay open, so I have them all on large tab control so I can flip between the open forms easily. Most of the forms use combo drop-down boxes as part of the filtering; like a drop-down listing all the staff, so one can be chosen to look at their jobs. And many of the queries under the forms use dynamic lookups to get data from other related tables.

It seems to have gotten worse in the last couple of months, so I am wondering if there was an update that impacted this area.

In any case, I would really like to solve this. Can anyone help?

I am in Sydney, Australia, by the way, so I may not respond immediately, due to the time zone difference to most users.

[#2]

@NeilClark 

This message often occurs when Access has used up all available connections.

See my utility Available Connections which you can use to check the issue and from that work out what needs to be changed to solve your issues

[#3]

@isladogs 

Thanks for that - I'll check it out and let you know how I get on.

[#4]

I have downloaded your Available Connections form and report and plugged them into the offending database and I can now see the connections dropping each time I open another form.

Previously, I have gone right through all of the code and made sure all objects and record-sets were closed and set to nothing when the form was closed. In fact, I can see that this seems to be the case, because when I open and close the forms now, the number of connections reverts to the previous level it was before opening them, so all items have been successfully closed.

The next question is; how do I further reduce the use of connections in my forms. I am obviously building my forms in a way that uses too many connections. How can I reduce that?

[#5]

@NeilClark 

As you may already know, Access has a total of 255 available connections.
Each open object with a record source/row source consumes 1 or more connections.
If the number of available connections drops too far, the error message you are getting may occur.

Closing recordsets after use is good practice and, as noted, this should allow Access to recover those connections.
However, your original post stated:

---

@NeilClark wrote:
The system has a number of forms that need to be opened and stay open, so I have them all on large tab control so I can flip between the open forms easily. Most of the forms use combo drop-down boxes as part of the filtering; like a drop-down listing all the staff, so one can be chosen to look at their jobs. And many of the queries under the forms use dynamic lookups to get data from other related tables.

---

Having all of these open at once is likely to be your issue. How many connections does all of this 'consume'?

Remember that subforms are loaded before the main form. So if you have a number of subforms on your tab control together with combos and dynamic lookups you are going to put Access under stress!

I suggest you only load each form as and when needed and close it when moving to another form / tab

OR only load the recordsource for each subform when it is activated. Similarly for your combos/ queries of possible. Then close each of these or clear the row source / record source when you move to another subform / tab

Hope that helps you move forward

[#6]

OK - thanks a lot for your wanting to help - it makes a big difference!

I will see how it works if I close each form when I open a new one. That seems to be the only way out that I can see just now. Will let you know how it goes.

[#7]

No problem. Sorry about the delay replying. I was offline most of today.
You didn't say how many connections were being used / how many are remaining before making any change to your app

[#8]

I did a test run with your little form open, starting with the opening page and then opening the others one-by-one. They each took about 24 to 40 connections, so eventually I ran out of connections and it bombed out.

[#9]

@NeilClark 

That is a lot of connections for each form and you state you have multiple forms open at once.

No wonder you are having issues

Very few, if any of my forms are that resource hungry.

Most of my forms use 4 connections. A form with 12 combos uses just 2 connections

Even a very complex form with 11 subforms only uses 18 connections

I suggest you do a detailed review of your app and consider changing your approach

Even before the app crashes, performance will suffer as so many connections are in use

If you want to upload a copy of your db with all sensitive data removed, I may be able to make some specific suggestions that may help

[#10]

@isladogs 

Hi, I am having a similar problems. Could I try your utility?

I tried to download but message stated that the page is not available

Thanks

Harris

[#11]

@Harris_St 

Hi
Sorry about the broken link. My old http website is no longer in use
The utility is still available from my new website: 

https://www.isladogs.co.uk/available-connections

I've also fixed the earlier link

A lot of people have contacted me in the past couple of days about this utility and it can be very useful to diagnose issues.

However, in case you aren't aware, there is another major bug in the recent update to Office 365 - version 2201 build 14827.20158 - which is triggering the cannot open any more databases error.

There have been a very large number of forum posts on the issue around the world.

MS are aware of the issue but have not yet commented publicly on it AFAIAA

[#12]

Hi,

If your version is  M365 Current Channel version 2201 build 16.0.14827.20158 then you are most likely one of the victims of the latest update bug. In this case you should read one of the many existing discussion about it here, especially this one.

Servus
Karl
************
Access News
Access DevCon

[#13]

@Karl_Donaubauer 

Hi Karl, 

Thanks for letting me/us know.

I have been tearing my hair out trying to understand what is happening.

At least I can now appreciate what is the cause for my teams databases falling over.

Regards,

Harris

[#14]

Thank you very much!
Harris

[#15]

I'm glad I found this thread. It lowered my anxiety level from high to moderate. I have a couple of questions: (1) does anyone know where or how I can keep tabs on this bug's eventual resolution? and (2) has anyone yet come up with some way to work around this bug while it's still unresolved?

[#16]

@fogelfish 

Please read the links provided by Karl and others. They are the most frequently updated discussions. And yes, the links include workarounds that are effective for many, if not most, cases.

However, to make it easier to find those links, here they are again for your benefit and those who may stumble onto the denouement of the thread only.

Here and here.