Cannot open any more databases - 3048

[JB]

I am hitting the wall with Jet error 3048. I found a couple of threads
referring to this error but in none of these discussions were any specific
numbers given. Does anyone know where a list of Jet's specifications can be
found and/or specifically what generates this error? The one thing that is
for sure is that it is not the number of databases since there are only two
and that I use CurrentDb. everywhere I need the database. This is apparently
a Jet problem rather than an Access problem so looking at the Access
specifications is no help. I will need to do some redesign of the app that I
am working on but don't want to do a lot of work only to find that I have
gone in the wrong direction.

I am using Access 2003, Jet 4, split database, workgroup security. Because
of the security, all the queries are stored with run under Owners permission
specified. I have a number of list boxes open at one time all based on stored
queries along with perhaps 15 or 20 subforms. Many of these are used in
conjunction with tab controls. Most of the listboxes, etc do not have lots
of records but the queries are sometimes comlex and so I want to minimize the
reinitialization of these lists as the user moves though the tabs of the tab
controls.

I have already done the rebuild from blank database, import everything
business and it makes no difference. Also, I make sure to .close all record
sets and set everything to nothing. I am assuming that Access does the
cleanup for listboxes when the containing form is close.

 

[Allen Browne]

Sounds like the list boxes are the issue here. Each one has its own
RowSource, which uses up one or more of these "databases."

Suggestions:

a) Do you have the same list boxes on multiple pages of the tab control? If
they are unbound (or bound to the same field in the same table), could you
cut these list boxes, and paste them directly onto the form itself? This way
they show through the tab control regardless of the page you have selected,
so the result is as if they were on multiple pages, but you have reduced the
number of list boxes.

b) If that is not feasible, are there several list boxes that have the same
RowSource? If so, you could solve the problem by using a call-back function
in their RowSourceType. These are fairly cryptic to wrap your head around,
but essentially they hold a static array of the values, and supply them out
of memory as each list box calls, so no database connections are needed.
There's an example of such a function in Method 2 of this article:
http://allenbrowne.com/ser-19.html

c) 20 subforms? That's fairly excessive. Do you really need them all loaded
at once? If not, you could place a single subform control on the form below
the tab control (i.e. NOT in its pages.) Then use the Change event of the
tab control to set the SourceObject of the generic subform. To the user it
looks like they moved page, but in practice you are actually loading a
different subform. This way there is only one subform open at a time, so you
are using far fewer resources.

d) If none of the above are suitable, the data structure might be an issue.
Last time I saw this, the guy had about 180 combos, one for each field. The
solution involved creating related records instead of 180 repeating fields.

e) De-initializing. You say you are using CurrentDb(). Make sure your
database variable is set to Nothing before exiting the routine. This needs
to be in the error recovery section, so it happens even after an error,
e.g.:
Function MyFunc()
Dim db As DAO.Database

Set db = CurrentDb()
'do something

Exit_Handler:
Set db = Nothing 'Set to Nothing before exit.
Exit Function

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler
End Function

 

[JB]

Thanks for the reply. I have used the callback method in the past and will
consider using it here. The many listboxes I have do not show records from
the same queries or tables so I can't make use of some of your ideas. I have
used many bound subforms for convenience of programming that are not strictly
necessary and which I can remove. The app I am working on has LOTS of small
lists of data that need to be displayed, some of which require expensive
queries drawing from several tables and involving vb function calls. It
appears that there will be no magic fix. I will have to deinitialize the
cheaper listboxes, remove subformes, etc. In some cases I am already using a
single subform control and switching the source subform as needed and I can
probably extend that idea further.

I am not using Set db = CurrentDb() directly but rather using it in the form
Set qry = CurrentDb.QueryDefs(..) and I am careful to set everything to
Nothing after using the qry, record set or whatever although I might have
missed one here and there. It is too bad that the compiler or some other
tool doesn't give any warnings about such missing statements since with
hundreds of queries, it is easy to leave one out.
I have now put a database counter edit control on my main window that runs
on a timer which tells me 1) all the forms do clean up properly when they are
closed and 2) the 3048 wall seems to be around 43 databases which doesn't
seem like a very large number considering Access allows thousands of open
tables, controls, etc

Thanks again

JB