Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202

    Form with 2 subforms causing -1104 and "connot open anymore databases"

    Good afternoon,
    I have been building my db on Access 2010 over the last several months and am pretty new to db building. The intent of this project is to audit and report based on those audits. The audit is quite large. It currently has 18 tables. Some with one to one relationships (they were made as one to one as there are not enough fields for the tables to include into one table) and others with one to many. In addition, there are 46 queries and various forms. The problem I am having is with the larger forms (see below).



    Form Bound Fields Unbound Fields Relationship
    Master 30 4 (dlookup) Parent
    Subfrm1 134 21 Child1
    Subfrm2 152 39 Child2
    Subfrm2 188 48 Child3

    I am having a couple of problems with this form. I am receiving error codes reserve error -1104 and “Cannot open anymore databases” with error code 3048. After a couple of entries, on my master form dlookup fields turn to #Error. My database is split be and fe. I have tried to merge back to one db and split again. Both options cause the errors. I have decompiled and CompactRepaired the db multiple times without correction. I have done much research, but with my knowledge base, I don’t seem to find a solution. From what I understand, my db is too large and some comments have been made about “loops.” I make sure all the forms are closed with only the one form open. Any of my VBAs are very simple. Can anyone please help me? I do not want to split this into multiple databases to make it work.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    This isn't by any means a large database.
    What may be happening is that objects are being opened and not closed when not in active use

    Access can have up to 255 connections to various databases objects (including hidden system tables) before it runs out of 'resources'
    Each open form, table, recordset etc reduces the total left available for use
    If you reach zero available connections, you will get error 3048

    So your task is to find out what is happening and fix issues

    To assist you, please try the attached utility database.
    The author is Ben Sacharich
    It contains one form - frmAvailableConnections

    Click image for larger version. 

Name:	Capture.PNG 
Views:	43 
Size:	6.6 KB 
ID:	33705

    Import this into your database
    Open it and leave it running in a corner of your screen as you open then close various objects
    Click Requery at each stage.
    If objects are correctly coded, you should recover all connections used by each object when it is closed
    Look for cases where that doesn't happen then look into the cause
    For example, a common problem is failing to close recordsets after use

    Remember, if the total available reaches 0, your database will crash with error 3048

    Good luck - hope this helps find the issues
    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

  3. #3
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Ridders52,

  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
    Quote Originally Posted by mlrucci View Post
    Ridders52,
    Yes that's me! ....
    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
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Ridders52,
    Thank you very much for your quick response. I used the tool that you supplied and ran it through the two forms I am having problems with. When I click into the subforms and enter data, as expected, my available connections go down. When I get to my last subform, it appears that my form is using all available connections. After the message "cannot open any more databases" is cleared, I requeried and it showed 15 connections. In addition, some of my data is not showing in the bound fields. With the number of fields I have currently in my form/subforms not being that excessive (as you expressed), why would I be using so many connections? Any suggestions as to simplify? I appreciate any help you have as I am new and perplexed.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Impossible to say without looking at your database
    Have you read the info shown when you click the '?' button

    How many connections are available before the last subform is opened / loaded
    Suggest you tick 'Log All Open Objects' then open the last subform
    Close it and check what hasn't been cleared

    As I previously mentioned, leaving recordsets open is a common issue

    Do you need to have all 3 subforms open at once?
    Could you change to using a tabbed form so only one subform is open at any time?
    Do you need so many fields to be shown in your subforms? Particularly subform 3 which seems to be the biggest issue

    You may find this link useful. It describes the limits for various part of an Access database
    https://support.office.com/en-us/art...8-98c1025bb47c

    One limit that may be an issue is maximum of 754 controls over the lifetime of a form (including any since deleted)
    Now I'm not sure if the subforms need to be counted but you have 616 fields listed in total

    Also limit of 255 fields in a table or query - that includes record sources for forms - subform 3 is close to that - 236 altogether
    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

  7. #7
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    unable to delete ..sorry, typo
    Last edited by mlrucci; 04-23-2018 at 06:31 PM. Reason: wrong buttons

  8. #8
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Ridders52,
    Again, thank you for your help. Very enlightening. I read your response and have done some trial and error.
    1. I really need to have all the subforms open so that I can review all pieces to make a decision
    2. I created the tabbed form, but all the subforms remain open and does not help disconnect the connections. Is there a vba code to make the subforms in the tabs disconnect when not in use? Am I missing something?
    3. Using the "Log All Open Objects" show only the open form and the frmAvailableConnections form.
    4. I copied and pasted the fields from one form to another to see if that would make as difference as there was the issue with max controls on a form. Didn't change anything.

    I am wondering if there is a way to use the tab form and able to switch from each subform quickly as an alternative to having everything show on one form. As I said before, all the subforms are staying open even though they are on the tab control form.

    I really appreciate your help in advance on this issue.

  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
    Sorry - it looks like i'm wrong about the tabbed forms.
    Just tested on a very large database using a form with 8 tabs and a total of 10 subforms.
    To my surprise this only reduced the available connections by 4 and it made no difference clicking through the tabs

    Click image for larger version. 

Name:	Capture.PNG 
Views:	38 
Size:	9.6 KB 
ID:	33710

    Of course a subform is merely another control in the main form so it won't be listed in the log
    Doing something in the subform may 'use up more connections

    Whilst that form was open I clicked on every possible link to other forms & reports on the various tabs, keeping each item open wherever possible
    The lowest figure I reached was 168 available connections

    NOTE: for info the database I was testing is very large - see below
    I've NEVER seen error 3048 using this db though I have seen it on another database

    Click image for larger version. 

Name:	DatabaseStatsSDA.PNG 
Views:	35 
Size:	195.4 KB 
ID:	33711

    I'm not sure I can give you any more specific advice without looking at your database
    Something dramatic must happen when subform3 is in use though I can't give any sensible suggestions at a distance
    Have you looked for recordsets, opened but not closed again?

    If you can post a stripped down version, someone can look at it
    Anyway, I'm logging off now as its very late here in the UK.
    If you do post anything, I'll try & look at it sometime tomorrow unless someone else has already done so.
    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

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Cross posted
    https://www.mrexcel.com/forum/micros...databases.html
    While cross posting isn't entirely against the rules in most places, it must be done in the correct and considerate manner. In the very least it can result in your post receiving limited or no attention at all. Please read this https://www.excelguru.ca/content.php?184 to understand why.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    If I recall correctly, certain controls (ie. listboxes, combos,etc) consume more than one database connection. I also think having lots of joins does also.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You do recall correctly....
    References to local tables or queries 'use' 1 connection. References to local tables 'use' 2

    I find it difficult to understand how any one subform can cause this issue if it is connections based (as it seems)
    Which is why I keep referring to recordsets being opened & not closed - possibly in a loop?

    BTW thanks are due to moke123 who originally uploaded the utility originally written by Ben Sacharich
    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

  13. #13
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    I have managed to do some more tinkering. I created a reduced version of my db to send to you and ran the frmAvailableConnections. When opening the forms in the reduced version/non split db, there are 253 connections. After opening the form, there are 249 connections. I then split the db and noticed that the connections were over my limit and I ran into the problem I have with the full version of my db (split or not). Could I be doing something wrong with the split? When I use the full version db non split, it uses all my connections. I attempted to attach the reduced version/non split db, but I keep getting failed upload error. So many challenges. Suggestions?

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by mlrucci View Post
    I have managed to do some more tinkering. I created a reduced version of my db to send to you and ran the frmAvailableConnections. When opening the forms in the reduced version/non split db, there are 253 connections. After opening the form, there are 249 connections. I then split the db and noticed that the connections were over my limit and I ran into the problem I have with the full version of my db (split or not). Could I be doing something wrong with the split? When I use the full version db non split, it uses all my connections. I attempted to attach the reduced version/non split db, but I keep getting failed upload error. So many challenges. Suggestions?
    Stick withe the unsplit db for now as you're adding an extra issue by splitting it ... and linked tables each use 2 connections whereas local tables use 1 ... so you'll hit your limit faster
    Remove anything not relevant to this issue, compact your database then zip it so it is small enough to upload
    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

  15. #15
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Thanks for the help. Attached is the zipped file. Thanks
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-17-2016, 10:55 AM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 4
    Last Post: 08-11-2014, 01:18 PM
  4. Replies: 2
    Last Post: 07-31-2014, 08:56 PM
  5. Replies: 2
    Last Post: 07-14-2014, 10:34 AM

Tags for this Thread

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