Results 1 to 9 of 9
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Too many open connections


    Sometime ago we found a little form that calculates the open connections. When our database were not split, we didn't have a problem with the error; "Too many open connections". It is a major challenge now when we split the database that the error comes up. It reports in some cases opening the same object in the split database has multiple times the open connections than the same object when not split. Please tell me there is a solution.

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    IIRC a local table will only consume one connection and a split table will consume 2. Other objects like subforms, combos and listboxes will also consume connections.
    Try limiting open forms in the background. If they have a persistant connection they are using up connections. Make sure recordsets are closed.
    Without more info its difficult to be more specific.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm pretty sure domain functions also consume connections as well - at least those that are on open forms/reports, either as part of the recordsource or as a control controlsource

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As both previous answers have indicated, any object you open with a record source or with a reference to a table will use up connections
    Linked tables use more connections than local tables

    Attached is the latest version of this utility in case you wish to make use of it..

    You can use this utility to monitor what connections you have open & ensure that any such connections are released when an object is closed
    Years ago, I used to occasionally get the too many connections error and it was always due to poor design on my part.

    I no longer see the error as I ensure that I only open objects when required (apart from a persistent connection to the BE which is deliberately left open at all times) and scrupulously close all objects / recordsets etc after use.

    The fact that you are getting these errors is an indication of serious design issues that you need to address

    Good luck
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hi Colin. I believe some time ago you referred me to the utility, thank you. Attach you may see the results. It is the same queries, but the one with the high numbers is after the split. The one after the split don't have data, and the ones before the split has data??? Is it right if it differs to the multiple of 15? Moke 123 said above 2 to 1. We have ideas to restructure. It is a finacial package and to get to the balance sheet, we need to bring most tables together.
    Click image for larger version. 

Name:	20190224 a.png 
Views:	22 
Size:	6.5 KB 
ID:	37569Click image for larger version. 

Name:	20190224 b.png 
Views:	22 
Size:	7.0 KB 
ID:	37570??

  6. #6
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    What does it mean when it says "from baseline"?

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Hi Colin. I believe some time ago you referred me to the utility, thank you
    hmm, wonder who referred Colin?

    Moke 123 said above 2 to 1
    A linked table consumes 2 connections where a local consumes one.
    If you have a lot of list boxes or combo boxes or recordsets, dcounts, dlookups, etc. those will consume connections (2 each if they are sourced to a linked table.)
    Its been a long time since I researched it but there might also be more connections used if there are joined tables used in their recordsources.

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    What does it mean when it says "from baseline"?
    Theres a max of I believe 254 or 255 connections of which you have 253 available when you open the form. 253 is the baseline.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Moke did indeed pass the original version of this utility on to me
    As a test, I have tried opening multiple items in a split database with both linked Access & SQL tables.
    I left the objects open whilst opening further objects which isn't how it would be used normally
    One particular very complex form has 11 subforms open at once
    That used 18 connections

    The number of connections for local & linked tables wasn't consistent - 1/2/4/6 connections observed in different cases
    Complex queries based on multiple tables used proportionately more connections

    After a while I had 5 forms (each with subforms), 10 tables and 3 queries open simultaneously
    Whilst the app was slowing down noticeably, it still had 177 connections available.
    I then closed each item and the total returned to the 251 connections I started with (I have a persistent connection to the BE)

    Things to check include:
    1. Do NOT use subdatasheets with your tables as each of those will be loaded at the same time & use up connections.
    2. If you have forms with multiple subforms, try & avoid loading each of these until these are actually needed
    3. Check that recordsets are closed and set to nothing after use
    4. Check that connections are restored to previous values when an object is closed
    5. Optimise your queries to rely on as few tables as possible
    6. Use a SQL BE rather than Access as this will allow you to manage your data & queries more efficiently
    ...and I'm sure much more!
    Last edited by isladogs; 02-25-2019 at 02:55 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Open connections
    By Perfac in forum Access
    Replies: 5
    Last Post: 07-27-2018, 03:44 AM
  2. DSN less connections
    By jaryszek in forum Access
    Replies: 23
    Last Post: 02-26-2018, 10:31 AM
  3. Problems with Database connections
    By Goinfory in forum Misc
    Replies: 1
    Last Post: 06-22-2015, 06:13 AM
  4. DSN-less connections, how?
    By tpcolson in forum Access
    Replies: 6
    Last Post: 02-17-2014, 07:53 PM
  5. How many FE connections to BE are too many?
    By ItsMe in forum Database Design
    Replies: 3
    Last Post: 12-06-2013, 04:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums