Error 3048: Too many databases open

[Doctor]

I need some help on this one! I've tried everything I could find on this. I
don't know where to go from here.

Double checked all Currentdb and rs to make sure that I have either closed
them and/or set them to nothing.

A couple of times when I used CurrentDB and it doesn't matter, I changed it
to dbengine(0)(0).

I haven't used any domain aggregate functions in queries. A minimal amount
of times a use a domain aggregate function in vba. Not too often. But when I
do, how long does that connection stay open?

None of my tables have a tremendous amount of fields. The main table has
about 25 fields.

The situation is that there is one frequently used process whereby a user
will have about 5 of the main forms open at the same time. It is on the 5th
form that not all of the combo boxes will load; and clicking them raises the
3048 error.

I put the structue of these five forms below just to provide some scope. As
far as I can tell, this puts me around 90 connections. I should be able to go
high than that.

I do have a split DB, but even if it doubles to 180, I should still have
around 40 to 60 connections to go.

At this point, restructuring the db would not be fun. I was almost ready to
distribute it when I started getting this error.

Here is a run down of the five forms:
Home Page:
main menu(based on table and uses two connections)
statistic queries for info (3 connections)
form is unbound

SearchPage
bound to query
two combo boxes

Main Company Page
bound to query
2 Combos
Subform 1: 6 Combos
Subform 2: 2 Combos
Subform 3: 1 Combo
Subform 4: 4 Combos
sfrm 5: 0 Combos
sfrm 6: 0 Combos
sfrm 7: 4 Combos
sfrm 8: 4 Combos
sfrm 9: 1 Combo
sfrm 10: 2 Combos
sfrm 11: 0 Combos
sfrm 12: 0 Combos
sfrm 13: 0 Combos

Contact Information Page
Bound to Query
Sfrm 1: 6 Combos
sfrm 2: 1 Combo
sfrm 3: 1 Combo
sfrm 4: 0 Combos
sfrm 5: 1 Combo

Registration Page
Bound to Query
3 Combos
sfrm 1: 6 Combos
sfrm 2: 1 Combo

 

[Dirk Goldgar]

I need some help on this one! I've tried everything I could find on this. I
don't know where to go from here.

Double checked all Currentdb and rs to make sure that I have either closed
them and/or set them to nothing.

A couple of times when I used CurrentDB and it doesn't matter, I changed
it
to dbengine(0)(0).

I haven't used any domain aggregate functions in queries. A minimal amount
of times a use a domain aggregate function in vba. Not too often. But when
I
do, how long does that connection stay open?

None of my tables have a tremendous amount of fields. The main table has
about 25 fields.

The situation is that there is one frequently used process whereby a user
will have about 5 of the main forms open at the same time. It is on the
5th
form that not all of the combo boxes will load; and clicking them raises
the
3048 error.

I put the structue of these five forms below just to provide some scope.
As
far as I can tell, this puts me around 90 connections. I should be able to
go
high than that.

I do have a split DB, but even if it doubles to 180, I should still have
around 40 to 60 connections to go.

At this point, restructuring the db would not be fun. I was almost ready
to
distribute it when I started getting this error.

Here is a run down of the five forms:
Home Page:
main menu(based on table and uses two connections)
statistic queries for info (3 connections)
form is unbound

SearchPage
bound to query
two combo boxes

Main Company Page
bound to query
2 Combos
Subform 1: 6 Combos
Subform 2: 2 Combos
Subform 3: 1 Combo
Subform 4: 4 Combos
sfrm 5: 0 Combos
sfrm 6: 0 Combos
sfrm 7: 4 Combos
sfrm 8: 4 Combos
sfrm 9: 1 Combo
sfrm 10: 2 Combos
sfrm 11: 0 Combos
sfrm 12: 0 Combos
sfrm 13: 0 Combos

Contact Information Page
Bound to Query
Sfrm 1: 6 Combos
sfrm 2: 1 Combo
sfrm 3: 1 Combo
sfrm 4: 0 Combos
sfrm 5: 1 Combo

Registration Page
Bound to Query
3 Combos
sfrm 1: 6 Combos
sfrm 2: 1 Combo

Are all those subforms (on the various forms) visible at once, or are they
on different pages of tab controls? In cases where a form has a number of
subforms on different tab pages, so only one of the subforms is visible at
once, you can work around this problem by clearing the SourceObject property
of each subform that is not currently visible. Then when the tab page
changes and a subform has to be made visible, you can set that subform's
SourceObject property (and the Link Master/Child Fields if necessary) and
clear the SourceObject property of the subform that is no longer visible.

 

[Doctor]

Dirk, thanks so much for getting back with me. Yes. My subforms are on
separate tab pages. I understand what you suggest.

How do I tell which tab page is active? When I clear the sourceobject, and
then reset it, how will the process know what to reset the sourceobject to?

If you could provide an example I could model off of, I sure would
appreciate it.

 

[Dirk Goldgar]

Dirk, thanks so much for getting back with me. Yes. My subforms are on
separate tab pages. I understand what you suggest.

How do I tell which tab page is active? When I clear the sourceobject, and
then reset it, how will the process know what to reset the sourceobject
to?

If you could provide an example I could model off of, I sure would
appreciate it.

The Value property of the tab control is the page index of the current tab
page, and the tab control's Change event fires whenever it changes from one
page to another. Code to do it might look like this:

'------ start of code ------
Private Sub tabMyTab_Change()

Select Case Me.tabMyTab

Case Me.pgFirstPage.PageIndex
Me.sfPageTwoSubform.SourceObject = ""
Me.sfPageThreeSubform.SourceObject = ""
Me.sfPageOneSubform.SourceObject = "sfPageOne"

Case Me.pgSecondPage.PageIndex
Me.sfPageOneSubform.SourceObject = ""
Me.sfPageThreeSubform.SourceObject = ""
Me.sfPageTwoSubform.SourceObject = "sfPageTwo"

Case Me.pgFirstPage.PageIndex
Me.sfPageOneSubform.SourceObject = ""
Me.sfPageTwoSubform.SourceObject = ""
Me.sfPageThreeSubform.SourceObject = "sfPageThree"

End Select

End Sub
'------ end of code ------

In the example above, I have hard-coded the names of everything, and have
assumed a separate subform control for each page. A number of variations on
this are possible. If every page has a subform and the subforms are all the
same shape and size, it would be possible to use a single subform control
that is actually *behind* the tab control -- not on any particular page, but
showing through to them all -- and simply swap that subform control's
SourceObject and Link Master/Child Fields properties each time the tab
changes pages.

Alternatively, if you have to use separate subforms for the different tab
pages, you could avoid hard-coding the names by using the Tag property of
each page to identify the subform control and source object appropriate to
that page. The code could either use the Tag property of the tab control
itself to identify what the previously current subform was (before the page
change), so that it can be cleared, or else loop through the pages to clear
the subforms for all non-current pages.

 

[Doctor]

That answers my questions. I have already been using the tag property to
store other information. And none of my subforms are the same size. So I'll
have to go the hard code route. Thanks for pointing me in the right
direction.