Split Database Issue - Cannot open any more databases

[KellieF]

I have a brand new database that I built and it works wonderfully until I try to split the database.  After split many of my queries that work fine before the split are now receiving the Cannot open any more databases error.  My version of Access (MS 365 Apps for enterprise) is currently up-to-date and I have set Trusted locations just to be safe.  I have tried a manual split and the database split tool in MS Access.  Some of the queries that will not open are fairly complex (and several deep) however they do work before the database split.  I am at a loss for what to try next.  I have never had this many issues with complex queries until trying to split my database.  Any ideas would be great I seem to have run out of ideas and am getting very frustrated.

[#2]

there is similar thread here: https://techcommunity.microsoft.com/t5/access/cannot-open-any-more-databases/m-p/2282166

[#3]

I have looked at that one but it still doesn't make sense to me that it works before I split the database, then doesn't after I do so.@arnel_gp 

[#4 · Accepted solution]

@KellieF 

Did you read my Available Connections article? It was discussed in the thread linked by @arnel_gp

As my article explains, an Access database can create up to 255 simultaneous connections to local and linked tables.

However, if a large number of connections are left open, the performance of the application will deteriorate & eventually it may crash with a message:
'Cannot open any more tables' or 'Cannot open any more databases'.  

One probable explanation for why the split database is causing issues is explained in this section of my web article:

Each reference to a local table, linked SQL table or query object uses 4 connections.
A reference to a linked Access table, spreadsheet or text file uses 6 connections.

So a form with links to 6 linked tables would be consuming 36 connections. 

You say you have a lot of complex queries.

You can try using my Available Connections utility to monitor what is going on

My initial advice would be to ensure that all items are closed when they are not currently required.

Also ensure your tables do not include subdatasheets. These badly affect performance and are confusing to end users.

Review the queries you are calling from form, combo box, and listview objects.  
Remove extraneous references to tables and sub-queries to reduce overhead.

[#5]

Thanks .  I have done almost everything you recommended.... next I am off to remove subdatasheets and see if that helps.  @isladogs