Solved

Count open databases in MS Access

Posted on 2016-11-02
7
28 Views
Last Modified: 2016-11-02
An application I have created at work continues to grow (using tabbed rather than overlapping windows) as I expand its functionality and now I am running into the "Cannot open any more databases" message which I understand is because I am touching the limit of 2048 connections with my open forms. So as to know which forms are the major offenders, is there any way I can count the connections by form? If MS Access knows when I am touching that limit then I would have thought there would be a way for me to know.
0
Comment
Question by:Rob4077
  • 4
  • 3
7 Comments
 
LVL 57
ID: 41869902
<<is there any way I can count the connections by form? If MS Access knows when I am touching that limit then I would have thought there would be a way for me to know.>>

Believe it or not, no.  And it's not connections, but references to tables.

 Every field, control, etc is a reference to  table, so they can add up quickly.

Jim.
0
 

Author Comment

by:Rob4077
ID: 41869918
What a blow! Just for clarification, if I create a complex query but only SELECT a few records from it, is it one connection per SELECTED field or does it count each field in each query?

Also, what is the general approach in a situation like this? Should I just create a second application? Every part of this app is used by all users regularly during the day so if I create a second app it will chew up more resources by having a second instance of MS Access loaded in memory, Or am I better off just changing to overlapping forms and forcing users to close forms as and when they finish with each?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 41869945
<<is it one connection per SELECTED field or does it count each field in each query?>>

 Each field counts, which is why you don't do SELECT *

<<Or am I better off just changing to overlapping forms and forcing users to close forms as and when they finish with each?>>

  I have to be honest here; bumping into this limit to a certain extent is either a bad DB design, bad development practices (SELECT *, nested queries 7 or 8 levels deep, etc), or an overloaded UI.

  While it's not as rare to bump into this as it once was, it's still pretty rare that someone bumps into this.   Only see one or two of these questions on EE per year.

 I would look at what your doing in the UI and refactoring that.

 This is an internal limit in Access, and there's no work around for it other than that.

Jim.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Rob4077
ID: 41870060
Regretably I am guilty all counts.  I was hoping there was an easy way to quickly identify the main offender. Unfortunately this is the first system I have built in a long time that evolved instead of being designed due to time constraints.  Thanks again for your kind assistance
0
 
LVL 57
ID: 41870113
<<Regretably I am guilty all counts. >>

 Well as a start, look at the queries.   Any place where you have SELECT *  is an easy fix and can give you a drastic reduction of table ID's being used.  Just that may get you by for now.

 I'd then tackle queries where nesting is heavy; anything more than a couple of levels is too much really and anything beyond four is way to much and should be re-done.   And this is so easy to creep into.   "I did that here and will just roll it in".   Then when you stand back and look, it's amazing how much gets repeated in some places.  
 
 While this takes time and analysis because it's easy to break things, it's still one of the simpler fixes overall.

 I'd then move onto the UI and break up complex forms along major functional points.   Popup's can work well here.   Present info to the user in a simplified form, then popup another form with the detail when they want to add/update.  Think about process flow and what the user does.

 Maybe right now everything is on tabs within a single form, where you could take a couple of tabs and split them off to another form.

Jim.
0
 
LVL 57
ID: 41870120
BTW on this

<< I'd then tackle queries where nesting is heavy; >>

The way to approach this is to create a query for each object (form, combo, list box, etc)  and tailor it specifically for that job.

 When you try and re-use query objects for a lot of different things, that's when you start getting extra overhead.  i.e. I need these fields for this, but not for this, and then before you know it, your adding in every field possible.

 Like I said, it's so easy to fall into this.

 You'll end up with a lot more objects in the DB, but it will be fast and efficient that way with the least amount of overhead.

Jim.
0
 

Author Comment

by:Rob4077
ID: 41870236
Thanks for the suggestions.  I will start work on it in the order you said first thing in the morning.  Thanks again
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now