Handling the Error: Cannot open any more databases

Question

I have encountered the annoying error "Cannot open any more databases" again during this process of my new database development.

On this new database, I have traced the cause to be majorly as a result of some table used repeatedly on several queries linked to to various subforms of my switchboard form which opens readily when I launch the database.

This gets better when I make and use new tables of the same tables on the FE instead of the tables on the BE.

1. How efficient are tables created from MAKE TABLE queries, are the original indexes and primary keys lost?
2. How can I maintain record consistency in the FE tables when they are created, edited or deleted from the data entry forms?
3. How ideal is this method, or is there a better approach to solve it.

Answer 1

"Cannot open any more databases" typically is encountered with very complex forms in which their are numerous subforms, combo boxes, listboxes and the likes which all create/use a database connection to get the data.  The solution is to simplify the form, use dynamic loading techniques (only load subforms, combo boxes, listboxes when they receive focus).  See: https://bytes.com/topic/access/answers/208627-cannot-open-any-more-databases for more information on this subject.

Typically, MAKE TABLES are to be avoided.

Answer 2

The solution is to simplify the form, use dynamic loading techniques (only load subforms, combo boxes, listboxes when they receive focus).

Please, help me on how I can do and achieve this. From what I read and understood from the link you included, this is ideally what I need to do to solve the problem because I have many subforms in tab control of my Switchboard form. I used late-binding as recommended by Allen Brown in his website to load the subforms but I need to also know and master how to disconnect or unload the subforms, combo boxes and List boxes when I move from page to page of the tab control or thereabout.

Answer 3

> typically is encountered with very complex forms

Agreed, as well as in databases created without much forethought, without referential integrity, with unnecessarily complex queries, and a myriad of other beginner mistakes. The OP needs to be MUCH more specific, ideally posting the database (stripped to the bare essentials, and without any PII) in a public place like a free OneDrive account, so we can take a look.