"Cannot open any more databases"

[rmccormack01]

I have been checking threads all morning and found a lot of info, but
not sure what to do in my case. I don't have the issue, but a couple
of users of my application do (more than one means it is likely a
problem with my software). When opening a particular form, they get
the "Cannot open any more databases" error message. The database is
Access 2003.

The form they are opening has one TabCtl with 7 tabs. Each tab has a a
group of associated fields to capture Registration data. Tab1, for
example would be used to enter people in an activity with 4
participants, Tab3 might be used to enter people in an activity with 8
participants. Data entry is made by selecting people from individual
dropdown lists (combo boxes) and populating other fields on the form.
Therefore, Tab1 would have 4 combo boxes and Tab3 would have 8 combo
boxes.

All combo boxes have the same RowSource (a query named
"qryRegDropList") and the query uses information from the parent form
to select from a master registration list (table).

A lot of the problems I have read about are due to a large number of
combo boxes with the same row source. What I think might be causing
the problem is that one of the tabs (for activities with up to 32
participants) has 32 combo boxes. In all, there are 74 combo boxes in
this TabCtl spread out over the 7 tabs, and each on has the same row
source.

Could this be the problem? Is this form trying to open 74 database
connections (or whatever it is called) everytime it is opened?
Alternatives???

Thanks in advance for any suggestions.

 

[John W. Vinson]

Could this be the problem? Is this form trying to open 74 database
connections (or whatever it is called) everytime it is opened?

Very likely.

Alternatives???

Normalize your data structure!!! If you have a table with 32 fields, one for
each participant, you're "committing spreadsheet", a venial sin punishable by
errors like this one. If one event has three, or 32, or 22915 participants,
the proper design is a 'tall thin' table with one record per participant:
EventID, ParticipantID, and possibly other fields about this participant in
this event.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[rmccormack01]

Very likely.


Normalize your data structure!!! If you have a table with 32 fields, one for
each participant, you're "committing spreadsheet", a venial sin punishable by
errors like this one. If one event has three, or 32, or 22915 participants,
the proper design is a 'tall thin' table with one record per participant:
EventID, ParticipantID, and possibly other fields about this participant in
this event.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thank you John. I do not have a table with a field for every
participant, but rather a table of all registrants (maybe up to 1200)
with their registration information. The registrant Name is one field
(it really is a tall-thin table). The queries that drive the values
in the combo box look to that table, compares registration data for
each potential particpant with the current data on the form, and
fetches that persons name (and a few other items). When Clicked, the
name goes into a text field adjacent to the drop-down. That person is
now "registered" as the person filling spot #1. There can be up to 32
combo boxes (drop-downs) on a form because ther an be up to 32 spots
in a given activity.

I saw in one post that multiple combo-boxes with the same RowSource
can create "number of connections" problems. I was trying to think of
a way that perhaps the RowSource would be entered only when the user
selected that drop-down to add a name. This would prevent all of
those queries (up to 74) from running every time the form was opened.
Am I missing something obvious?

Thanks again for your response.

Bob

 

[John W. Vinson]

That person is
now "registered" as the person filling spot #1. There can be up to 32
combo boxes (drop-downs) on a form because ther an be up to 32 spots
in a given activity.

And what will you do when the rules change and there can be 50 spots? Redesign
your form, rewrite all your code...???

Where and how are the people assigned to these "spots" stored? If just on the
form, that's not terribly useful, since forms don't store data (only display
it); if in a table, what's the structure of that table?

If one Event can have up to 32 people assigned to "spots" in that event, you
have a simple, classic many to many relationship; the usual way to store the
data would be a table with fields for EventID, PersonID, and perhaps
SpotNumber, and the usual user interface would be a Form based on the event
with a subform based on this table. You'ld have *one* combo box for the person
- on the subform - and enter up to 32 rows. You could have VBA code in the
subform's BeforeInsert event to prevent adding too more people than there are
spots.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

(e-mail address removed) wrote in
:

Could this be the problem?

Yes. Sounds like a terrible design to me, but then, I probably don't
have a good idea of what it is you're actually doing. Your
explanation was quite confusing, and from what I got, your interface
is not like anything I've ever encountered anywhere. Sounds to me
like you ought to be using a subform, because in that case, you
could have as many rows as you like. I'm not entirely certain if
each instance of the combo box in the subform (one for each row)
would use up table handles of its own, but if it is, at least you'd
only be using as many as you had rows.

 

[rmccormack01]

I tried to avoid getting into specifics, but I think that by doing
that I was unable to really describe my situation. Let me try again.
I wish I could attach a picture, but can't figure that out either.

I have a tournament bracket program with multiple divisions and
multiple weight classes for each division.

For example, there is a Tournament Bracket for Seniors Division, 150
pounds weight class. There is a tblPairings table that has one record
for each Bracket. The unique fields on the record are Division and
WeightClass. The frmPairings form therefore has one record (form) for
each bracket. Because every bracket can be a different size based on
the number of participants (e.g., 4-person, 8-person, ..., 32-person),
there is a tab control on the form that has tabs for each possible
bracket size (a total of about 7 tabs (pages).

If the bracket size is 8, the page on the tab control that has the
bracketing data entry fields for an 8-person bracket is selected (I
could have used separate sub-forms, but the tab control seemed to work
best).

With the 8-person bracket page selected, I now have to select which
registrants are going to compete in this bracket.

There are 8 combo boxes on this page that represent the 8 spots I am
going to assign.

combo box 1: select person A for Match 1 => enters him in spot 1A when
name is clicked
combo box 2: select person B for Match 1 => enters him in spot 1B when
name is clicked
combo box 3: select person A for Match 2 => enters him in spot 2A when
name is clicked
combo box 4: select person B for Match 2 => enters him in spot 2B when
name is clicked
combo box 5: select person A for Match 3 => enters him in spot 3A when
name is clicked
combo box 6: select person B for Match 3 => enters him in spot 3B when
name is clicked
combo box 7: select person A for Match 4 => enters him in spot 4A when
name is clicked
combo box 8: select person B for Match 4 => enters him in spot 4B when
name is clicked

The RowSource for all combo boxes is the same; it is a query that uses
the Registration table (all registered participants in the tournament)
and selects only those that match the Division and Weight Class shown
on this record (in this example, all Senior division registrants that
weigh 150 pounds). When a name is selected, it is entered into the
proper spot on the bracket.

This has worked perfectly for several years (Access97/Access2000/
Access2003) on Window98SE, Windows2000, XP, Vista, and for MOST users,
Windows 7 (32-bit and 64-bit). However, I have had 2 users say that
they get the "Can't open any more databases" error when they open this
form. Both of them are using Windows 7. Since the tournament must
accomodate different size brackets there are a total of 74 combo boxes
on the form - 4 on the page for 4-person brackets, 8 on the page for 8-
person brackets, 32 on the page for 32-person brackets, etc.

Hence my question - could it be the large number of combo boxes
causing the error? Does each combo box generate a "handle" when the
form is openned (I ahve seen the term in other threads, but don't
really know what they are)? Because I am using a tab control page,
could ther be an issue with the majority of the controls on the form
being "hidden' when only one page is being used?

Lots of questions, not a lot of symptoms to point me in any specific
direction. Any help is appreciated.

 

[Bob]

I tried to avoid getting into specifics, but I think that by doing
that I was unable to really describe my situation. Let me try again.

I have a tournament bracket program with multiple divisions and
multiple weight classes for each division.

For example, there is a Tournament Bracket for Seniors Division, 150
pounds weight class. There is a tblPairings table that has one record
for each Bracket. The unique fields on the record are Division and
WeightClass. The frmPairings form therefore has one record (form) for
each bracket. Because every bracket can be a different size based on
the number of participants (e.g., 4-person, 8-person, ..., 32-person),
there is a tab control on the form that has tabs for each possible
bracket size (a total of about 7 tabs (pages).

If the bracket size is 8, the page on the tab control that has the
bracketing data entry fields for an 8-person bracket is selected (I
could have used separate sub-forms, but the tab control seemed to work
best).

With the 8-person bracket page selected, I now have to select which
registrants are going to compete in this bracket.

There are 8 combo boxes on this page that represent the 8 spots I am
going to assign.

combo box 1: select person A for Match 1 => enters him in spot 1A when
name is clicked
combo box 2: select person B for Match 1 => enters him in spot 1B when
name is clicked
combo box 3: select person A for Match 2 => enters him in spot 2A when
name is clicked
combo box 4: select person B for Match 2 => enters him in spot 2B when
name is clicked
combo box 5: select person A for Match 3 => enters him in spot 3A when
name is clicked
combo box 6: select person B for Match 3 => enters him in spot 3B when
name is clicked
combo box 7: select person A for Match 4 => enters him in spot 4A when
name is clicked
combo box 8: select person B for Match 4 => enters him in spot 4B when
name is clicked

The RowSource for all combo boxes is the same; it is a query that uses
the Registration table (all registered participants in the tournament)
and selects only those that match the Division and Weight Class shown
on this record (in this example, all Senior division registrants that
weigh 150 pounds). When a name is selected, it is entered into the
proper spot on the bracket.

This has worked perfectly for several years (Access97/Access2000/
Access2003) on Window98SE, Windows2000, XP, Vista, and for MOST users,
Windows 7 (32-bit and 64-bit). However, I have had 2 users say that
they get the "Can't open any more databases" error when they open this
form. Both of them are using Windows 7. Since the tournament must
accommodate different size brackets there are a total of 74 combo
boxes on the form - 4 on the page for 4-person brackets, 8 on the page
for 8-person brackets, 32 on the page for 32-person brackets, etc.

Hence my question - could it be the large number of combo boxes
causing the error? Does each combo box generate a "handle" when the
form is opened (I have seen the term in other threads, but don't
really know what they are)? Because I am using a tab control page,
could there be an issue with the majority of the controls on the form
being "hidden' when only one page is being used?

Lots of questions, not a lot of symptoms to point me in any specific
direction. Any help is appreciated.

 

[John W. Vinson]

I tried to avoid getting into specifics

Well...

Do.

You've been talking about your *FORM*.

David and I have been asking about your *TABLES*.

Data is not stored in forms; Forms are just windows, tools to manage data
stored in Tables. The data is stored in Tables, and *only* in Tables. A set of
well normalized tables is the basis of any good Access application.

A set of improperly designed tables could well be the cause of your problems.
We don't know, because you have not described your tables.

What tables do you have? How many fields? What is the Recordsource of this
form? How do you store a "bracket" in your tables?

To emphasize again: let's leave the Form out of the discussion for the moment,
until we can understand the foundation, there's not much point in talking
about the walls and girders.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

m:

Hence my question - could it be the large number of combo boxes
causing the error?
Absolutely.

Does each combo box generate a "handle" when the
form is openned (I ahve seen the term in other threads, but don't
really know what they are)?

Assuming the rowsource is a SQL string and not a callback function
or a list, each combo box uses at least TWO table handles, one for
each table in the FROM clause of the SQL SELECT and one for the SQL
statement itself. So, this rowsource uses TWO table handles:

SELECT tblLookup.*
FROM tblLookup

If you have 74 combo boxes with that rowsource, you're using 148
table handles.

Because I am using a tab control page,
could ther be an issue with the majority of the controls on the
form being "hidden' when only one page is being used?

They are not visible but they are still there.

As John Vinson points out, you don't explain how your form relates
to your data. It sounds like an unbound form, and I would suggest
that you instead use a bound form. In that case, you could use a
datasheet or continuous subform to edit the data, and there'd be
only one combo box defined (with an instance of it for each row of
your subform). It's also possible with an outer join to replicate
the list of all possible records before those records are created.
This form shows an example of it:

http://dfenton.com/DFA/examples/NKF/Meds.jpg

There are no actual records stored yet, but there's a list of
possible medications. The record is created as soon as you edit the
combo box to choose Yes/No/Unknown. You could do the same, with a
table of the places for the tournament linked via left join to the
table where the actual tournament records are created. The SQL is
just a little tricky and I'd have to look it up. If you're
interested I'll post it.

But, as John says, at this point, we need to talk data structure,
because that's the only way to understand how the interface you've
outlined is used, and thus, the only way we can ever suggest
workable solutions to the problem.

 

[Bob]

Gentlemen, Thank you for taking the time to respond. I apologize for
taking so long to recognize your efforts. What you have said about
the tables has sunk in and I started a reply to explain the table
structure and saw that it would probably be very confusing. I started
this app over 15 years ago in Access 2.0. It grew as time went on and
I added and added and added features. In order to accommodate larger
brackets (go from 16-person brackets to 32-person brackets) I had to
add more tables specific to the larger brackets because the previous
tables were "maxed out" (250 fields). Very likely, my problems at
least begin there.
I have not forgotten you or your help, but I am looking into the table
issues myself before I confuse you more. Thanks again for your input
so far.

 

[John W. Vinson]

Gentlemen, Thank you for taking the time to respond. I apologize for
taking so long to recognize your efforts. What you have said about
the tables has sunk in and I started a reply to explain the table
structure and saw that it would probably be very confusing. I started
this app over 15 years ago in Access 2.0. It grew as time went on and
I added and added and added features. In order to accommodate larger
brackets (go from 16-person brackets to 32-person brackets) I had to
add more tables specific to the larger brackets because the previous
tables were "maxed out" (250 fields). Very likely, my problems at
least begin there.
I have not forgotten you or your help, but I am looking into the table
issues myself before I confuse you more. Thanks again for your input
so far.

Just bear in mind: "Fields are expensive, records are cheap". If you're
continually adding new fields, and going to one-to-one tables because you're
running out of fields, then your table design IS WRONG!! A 25-field table is
very, very wide; I've needed as many as 60 fields in a table and found that to
be a painful decision. There are very very few Entities with 250 independent,
nonrepeating attributes (well, there are, but they don't typically appear in
Access databases).

If you have (for instance) a 32 person bracket, and have a table with 32
fields for the 32 people, you have a spreadsheet, not a proper table. Each
person should get a new RECORD in a related table, not a new field.

Don't hesitate to post your tentative table design here, or (perhaps better
since more people will see it) in one of the web forums in my .sig; good luck
with your redesign.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com