Forum Discussion

KellieF's avatar
KellieF
Copper Contributor
Nov 07, 2022

Split Database Issue - Cannot open any more databases

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 ...
  • isladogs's avatar
    Nov 08, 2022

    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.

Resources