Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't open any more databases (A97 SR2)

    An A97-SR2 application is developed on standalone Win98SE, and is run
    in the production environment primarily on Win2000 workstations
    connected to a Win2000 server. Many of the application forms are
    complex with multiple tab sheets and subforms. Typically, the subforms
    do not have a defined recordsource until their tab is selected by the
    user.

    When running, the application frequently displays the infamous "Can't
    open any more databases" error, although I haven't seen one on my
    development machine for a long time. I attribute this to one of two
    things:
    - the client pushes the software harder than me, thus opening more
    databases, or
    - there is a fundamental difference between running A97 under Win98
    and Win2000

    I understand that the long-term solution is to simplify the forms, but
    in the meantime, I have the following questions:

    1) *Is* there a difference between Win98 and Win2000 regarding the
    "Can't open more databases" error?

    2) Are there any tools to monitor the current number of open databases
    that contribute to the error message? It would be a whole lot easier
    to diagnose which forms to modify if I could measure which ones were
    the hogs.

    3) Has the limit been changed under A2002? I've been reluctant to
    upgrade because I haven't perceived any benefit for this application
    (it is purely Jet-based, using just DAO). However, if this problem is
    eliminated under A2002, that would be a compelling reason to upgrade.

    Thanks for any advice.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Can't open any more databases (A97 SR2)

    Jack,

    I don't think I've ever seen the "Can't open any more databases" error (nor can I find a reference to it in the MSKB). What exactly is causing this; just opening forms/subforms, or perhaps doing alot of "set db = ....." in code? Are the tables in the frontend linked to multiple backend databases?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't open any more databases (A97 SR2)

    Mark

    Unfortunately, I am going from memory, and today I can't find a reference in MSKB either! But i generated a list of error codes from the AccessAndJetErrorsTable function found in the A97 Help system, and found that error #3048 has the ErrorString that I recall and that the client is experiencing. I *did* find a reference to it about a year ago in MSKB, and it is (apparently) well understood by MS.

    As I recall the KB article, there is a limit of 2048 "database handles" or "database references" that can be open concurrently. An earlier limit was 1024, but A97 or A97 SR-1 or A97 SR-2 increased the limit to 2048. I've run into it a couple of times, but only with complex forms that include lots of subforms and combo boxes. There is not a direct 1:1 relation between number of form controls and the 2048 limit, so my form isn't quite as complex as you might think from that number! Also, the error occurs when the user has multiple forms open concurrently, which is a requirement/habit/nice feature of the way the client uses the application.

    So if you want to see the error, you must open multiple, complex forms that contain lots of combos, subforms, or list boxes. Not sure whether the database functions (e.g. DLOOKUP) contribute to the count. Nor am I sure how Set DB = xxxx and Set DB = Nothing contribute to the count. That may be part of my problem -- not cleaning up properly in the form's code after using one of those statements.

    My stopgap solution has been to reduce the complexity of the form (as advised by the elusive MSKB article!) But that's part of the problem because I can't tell exactly how much effect is caused by, say, eliminating a single combo box.

    So, if you haven't seen the error, that means that either 1) you design less complex forms than I have done, or 2) you use a different platform that has a different limit. I was really hoping to get a comment from somebody such as "yes, I used to experience that error under A97, but it no longer is an issue under A2002". Or not!!

    Thanks for looking.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Can't open any more databases (A97 SR2)

    I've seen (and done) alot of complex forms, but I've never seen that error. However, it may be (as you suggested) that it is the simultaneous use of several of these complex forms that reaches the trigger point.

    There is one additional trick you might use when dealing with subforms on tab controls. Instead of putting a subform on each tab page, I put a single subform on the main form behind the tab control; any control placed like this will "show thru" on all tab pages as if it was on each page. Then in the tab control's "Change" event, I change the sourceobject of the subform control and the linking fields (I also have a little routine to change the subform control's dimensions based on the subform itself). This also helps forms load faster and speeds moving from record to record once the form is opened.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't open any more databases (A97 SR2)

    I like the idea of the single subform, but I'm not sure whether it would work in this situation. EAch tab currently contains one or more subforms and various numbers of standalone controls. I guess I would have to package each of those "combinations" into its own subform, and then put *that* into a single subform on the main form?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  6. #6
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't open any more databases (A97 SR2)

    <P ID="edit" class=small>(Edited by carbonnb on 23-Dec-02 10:47. Edited to add a reference I just came across!!)</P>Jack,

    IIRC, the Too Many Databases Open error is caused by having too many recordset references open. A recordset reference is opened with each combo or listbox being filled based on a table or query, the main bound form query, any subform query, the combos and listboxes in subforms and things of that nature. Pluss any that you open in code.

    I think if you move the controls to a sub-form, you will run into the limit faster, because now you are adding a subform recordset to the mix.

    I think, you may actually need to rethink the design of the entire form.

    I think this link may just help you out in understanding the error http://www.mvps.org/access/bugs/bugs0010.htm
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't open any more databases (A97 SR2)

    And now a bit more info.

    It's not just the currently active from that adds to the count. It's all open forms, even if they are hidden, that increases the count.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Can't open any more databases (A97 SR2)

    >>I guess I would have to package each of those "combinations" into its own subform, and then put *that* into a single subform on the main form?<<

    You could do that. It will depend alot on the circumstances. It works best when there are subforms with alot of records that aren't displayed that often. For example, a subform on one tab might display all history for that account; but that tab is seldom selected by the user (but it is requeried each time an account is displayed). Sometimes I also add code to toggle the visible property of that "master" subform control, based on which tab page is being displayed (if certain pages don't need a subform).

    Since you are apparently desparate for ways to reduce the number of open recordsets, here is another possible trick. Quite possibly, you have certain combo/list boxes based on tables that are not likely to change during a session. For example, you may be keeping open a recordset merely to display a combo box that has in it ("Active", "Inactive", "Withdrawn"). You could set the combo box source to a ValueList. When the form opens, open the table in code, build the valueList string, then close the table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't open any more databases (A97 SR2)

    Thanks Bryan
    WRT the Jet 3.5 SP3 update -- how does one know for certain whether it is installed? Help > About from Access mentions the SR version of Access, but it is silent about the Jet version.

    I was onsite with the client today, and found that the error mostly appears when they launch a report from a command button on the complex form. It's not so much the form itself that is causing the problem. At least that gives me something to work with.

    Thanks to you and Mark for your help.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  10. #10
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't open any more databases (A97 SR2)

    The Component Checker may be able to do it, but a fool proof way would be to read KB Article 172733 and compare the file Versions and Data/Times installed with those listed.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't open any more databases (A97 SR2)

    FWIW - I just had a positive report that everything is working well now, and that the "can't open more databases" error is a thing of the past. A couple things contributed to the fix.

    1. Access / Jet versions. Some of the workstations were not running the latest Access Service Release or Jet Service pack.
    2. Cleaned up all recordset object variables by setting them to Nothing in the code.
    3. Made a "kill-it" button that removed all the recordsources for all the subforms -- to be used as a last resort.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •