A2K: Cannot open any more databases (yet again)
downwitch
I've read through every post on this newsgroup--and numerous
others--on the subject of Jet's limits to locks (2048, if I've done my
homework) and how they cause this error (3048, for those who are
counting). And I'm tearing my hair out over it just the same, not
unlike everyone who hits it.
General response seems to be "consider reducing the number of IDs your
forms are summoning"--which I've done, many times over, and over the
course of many months. So I will burden you with some details:
1. I handle any DAO code very carefully--use a single global database
variable, never set or unset a recordset in error, etc. When I hit
this error, the maximum number of recordsets opened on my global
database is about 20. And they're all quite simple, mostly
configuration tables that are frequently read for one parameter or
another; I maintain the recordset in memory, which speeds things up a
lot over accessing the table for each read.
2. It's a job-management application. The client, contact, and
supplier forms are all horrendously complex, of course (usually two
subform, plus between four and eight list/combo boxes), or I wouldn't
be getting the error. But they never pull more than one master record
at a time. And the main job form itself is totally unbound, mostly
handled through OOP and callbacks.
3. The way I get the error is to have the client, contact, supplier,
and job forms all open at once. Which the users do because they flip
back and forth between them constantly, using context menus. They're
very happy with this, and I'm loathe to change it at this point--If I
close the forms as opposed to merely hiding them, then it's going to
cost me the time to reopen them each time (the bound forms can take up
to 5 seconds to load), and the tests I've done don't seem to
necessarily prevent the error.
4. The application is split; there are about 125 linked tables.
5. This is Access 2000, with all Jet/Office SPs applied.
My question(s):
Is there any way to see how these 2048 locks are written? Where they
are written? So that I could see which processes are maxing it out?
The locks written by bound forms and controls don't seem to expose.
Any more tips on lightening things up? Should I write callback
functions everywhere, instead of using bound queries for lists? Is
there a way to show a continuous subform NOT based on a table/query
recordset?
Again, I'll point out that this is not a problem of DAO, as I'm very
careful with explicit dimensioning and closing/setting to
nothing--it's a problem of a complex (but generally very
smooth-running) application rubbing up against the Jet's limitations.
Thanks for reading. Any suggestions would be welcome.
Andy
<<>>
> Again, I'll point out that this is not a problem of DAO, as I'm very
> careful with explicit dimensioning and closing/setting to
> nothing--it's a problem of a complex (but generally very
> smooth-running) application rubbing up against the Jet's limitations.
>
> Thanks for reading. Any suggestions would be welcome.
I'd think about switching to msde or sql server as the back end.
And
Bear in mind your experience for any future development work.
TR
databases and recordsets open. Set it up to call from an autokey macro, or
call in the debug window.
Start opening forms and using the application. Periodically check the
database/recordset count, see where/when it is increasing, or more to the
point NOT decrementing when you think it should. You should be able to
find a form or process that is increasing the count, but not decreasing
when the form closes or process ends.
Do you use any nested/heirachical classes? I found in some nested classes
(i.e. a parent class containing a collection of subclasses) that although
I set the parent class to nothing, and it was no longer accessible, that
any databases/recordsets that had been open in the subclasses were STILL
in memory. This in spite of the fact that all classes had terminate events
that set their objects to nothing. Because the subclasses were being
referenced in a collection, they would not terminate. I wrote "Destroy"
procedures for each class that explicitly cleaned up any objects in the
class, and calls the destroy method of any child classes, and removed
items from any collections. Then in my code I just used:
objParent.Destroy
set objParent = Nothing
This eliminated all object references from memory.
Public Function CountDatabasesOpen()
Dim wks As Workspace
Dim intDBCnt As Integer
Dim intRstCnt As Integer
Dim i As Integer
Dim strResult As String
Set wks = DBEngine.Workspaces(0)
intDBCnt = wks.Databases.count
strResult = "Total Databases: " & intDBCnt
For i = 0 To intDBCnt - 1
intRstCnt = wks.Databases(i).Recordsets.count
strResult = strResult & vbCrLf & "DB(" & i & ") Recordsets: " &
intRstCnt
Next i
Set wks = Nothing
msgBox strResult
End Function
downwitch
would never have occurred to me to query the workspace, duh.
And yes, I know about that explicit-destruction problem, found that
one a while back, especially as one of my subclasses was collection of
recordsets. But as they were still being called off the main global
database variable, I was able to find that out pretty easily.
One follow-up question: in a couple of continuous forms, I built some
queries using a number of subqueries to provide lookup names. I did
this because the access time was, in general, faster than running
lookup code--you don't get that split-second lag time when you call up
a fresh record on the screen. But was this a bad idea? Does anyone
else have experience testing both scenarios--i.e. a query built on a
master table with, say, eight lookup subqueries, vs. the same master
table with eight lookup functions that explicitly terminate dbs when
called?
Thanks again for your time. I will follow this up once the testing
has helped me understand a little better where the stress is
occurring.
TR <t_NoSpam_redick@Mind_NoSpam_spring.com> wrote in message news:<3EDE056B.D32AA9BB@Mind_NoSpam_spring.com>...
David W. Fenton
<29b70902.03060...@posting.google.com>:
>I've read through every post on this newsgroup--and numerous
>others--on the subject of Jet's limits to locks (2048, if I've
>done my homework) and how they cause this error (3048, for those
>who are counting). And I'm tearing my hair out over it just the
>same, not unlike everyone who hits it.
>
>General response seems to be "consider reducing the number of IDs
>your forms are summoning"--which I've done, many times over, and
>over the course of many months. So I will burden you with some
>details:
>
>1. I handle any DAO code very carefully--use a single global
>database variable, never set or unset a recordset in error, etc.
>When I hit this error, the maximum number of recordsets opened on
>my global database is about 20. And they're all quite simple,
>mostly configuration tables that are frequently read for one
>parameter or another; I maintain the recordset in memory, which
>speeds things up a lot over accessing the table for each read.
That and having all those forms open as you describe below are
exactly where you hit the limits.
I am not certain that it's 2048 table handles at a time, or 2048
table handles in a *session*. I don't belive it's the latter or the
work I did to fix the app of mine where this happened would not
have been effective (I encountered it before Jet SP2 came out, so
the limit was 1024 table handles; my app was replicated, too, which
makes you hit the ceiling sooner).
I have never done anything like what you're doing with using
recordsets for global configuration settings. Some alternatives:
1. put all your global configuration variables into a single
recordset, either by using a single common table, or by creating a
UNION query for the initial loading.
2. use global variables, initialized in the startup routine where
you're now initializing your configuration recordsets. You'll need
to do something about re-initializing after a code reset, but
you're having to deal with that with your recordsets, so it's no
big change.
3. wrap your global configuration data in 1 or more class modules
that are self-healing. Make the configuration parameters be public
members of the class, assigned when the class is initialized. If
the code is reset, the next call to a member of the class module
will re-initialize the values, if you're populating the members of
the class in the class's INITIALIZE event.
I'm assuming you're not referring directly to the recordsets in
code, but have a wrapper function that searches a recordset for the
appropriate value and returns it. You might be able to adapt that
code to get the data from one of these other structures, though I'm
not sure I'm seeing how. Eval() might work for a global variable
name, but it isn't going to work for the members of a class module.
You'd probably have to maintain an internal collection or an array
and have a public function to get data out of it. But it's
certainly possible to change the storage method without needing to
rewrite your code *if* you've already got a buffer level of
retrieval code in place.
>2. It's a job-management application. The client, contact, and
>supplier forms are all horrendously complex, of course (usually
>two subform, plus between four and eight list/combo boxes), or I
>wouldn't be getting the error. But they never pull more than one
>master record at a time. And the main job form itself is totally
>unbound, mostly handled through OOP and callbacks.
Number of records has zilch to do with it. It's the number of table
handles. That's not the same as number of recordset, either,
because one recordset has at least 2 table handles (one for the
recordset, one for the table the recordset is drawn from).
>3. The way I get the error is to have the client, contact,
>supplier, and job forms all open at once. Which the users do
>because they flip back and forth between them constantly, using
>context menus. They're very happy with this, and I'm loathe to
>change it at this point--If I close the forms as opposed to merely
>hiding them, then it's going to cost me the time to reopen them
>each time (the bound forms can take up to 5 seconds to load), and
>the tests I've done don't seem to necessarily prevent the error.
Don't assign recordsources until the forms are revealed. At the
very least, do this for the subforms and the combo boxes.
>4. The application is split; there are about 125 linked tables.
>
>5. This is Access 2000, with all Jet/Office SPs applied.
>
>My question(s):
>
>Is there any way to see how these 2048 locks are written? . . .
They aren't locks -- it's table handles, like window handles in
Windows. There is a limit of 2048 simultaneous table handles.
> . . . Where
>they are written? So that I could see which processes are maxing
>it out? The locks written by bound forms and controls don't seem
>to expose.
I don't know that there is any way to find out where they are used.
>Any more tips on lightening things up? Should I write callback
>functions everywhere, instead of using bound queries for lists?
Well, for combo boxes, you don't need callback functions. Instead,
use the combo box's OnChange event to set the rowsource. Of course,
if you want the chosen value displayed, then you need to leave the
rowsource, unless you set it so that it will display items not in
the list. I guess you could have the OnEnter event set it to
require a choice from the list, set the rowsource in the OnChange
event (after, say, 3 characters have been typed), then in the
OnExit, clear the rowsource and set the LimitToList property back
to FALSE.
Sounds awful, I know.
Another alternative would be to populate the rowsources with lists
when the forms are loaded. This works only up to the string length
limitation of the value list rowsource (1024 or 2048 characters,
can't remember which). You could easily write a global function
that you would pass a combo box SQL row source and have it evaluate
if the resulting value list is too long, and return the SQL if it
is and the the value list if i's not. This would be a lot less work
than writing all those callback functions.
I would definitely consider using the lists populated in the form's
OnLoad event *if* most of these combo boxes are relatively short
lists. Of course, combo boxes should be relatively few rows in the
first place, or you're misusing them.
>Is there a way to show a continuous subform NOT based on a
>table/query recordset?
Nope.
>Again, I'll point out that this is not a problem of DAO, as I'm
>very careful with explicit dimensioning and closing/setting to
>nothing--it's a problem of a complex (but generally very
>smooth-running) application rubbing up against the Jet's
>limitations.
You have too much stuff open. You have to get away from that.
Definitely kill the 20 open recordsets for configuration
information. I have never heard of anyone using that method.
And then use the most efficient methods for populating combo boxes.
And for subforms, don't load the subform until it is to be visible
to the user.
And consider if some of the forms that you are keeping open are
lightweight enough that they can be opened on demand without
noticeable delay, instead of left open at all times.
The key point: have assigned recordsources/rowsources only for the
items that are currently visible to the user.
And, just so you know, I ran into the issue for *exactly* the same
reason: I had 3 heavyweight forms that the users were switching
back and forth between, and closing and reopening them was just
taking too long.
But, I did manage to get it fixed, and with half the table handles
you have available.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
David W. Fenton
<3EDE056B.D32AA9BB@Mind_NoSpam_spring.com>:
>I have run into this problem as well. Here is a function that
>enumerates databases and recordsets open.
That won't tell you anything at all about the real number of table
handles in use.
downwitch
questions. If there are links answering these questions, please feel
free to point me to them, I just can't find the explanation I need.
1. Why does a new database get opened every time a bound form gets
opened? Is this so that the forms can be discretely updated?
2. What is that 2048 figure really representing? I get the "cannot
open any more..." error at 5 databases open, with a combined total of
15 recordsets open. Do the locks depend on the number of records per
set? I don't see how 75, or let's say 150 locks, gets me anywhere
near 2048...
3. What would make the same recordset open twice in a database? (I'm
working on figuring out this one myself of course...) This is the
case in two different places, mainly with subform recordsets.
Thanks again for your wisdom. That code is really helpful, and very
easy to use.
TR
But it may be worthwhile confirming that he does not have dao open
database issues, since the error was "cannot open anymore databases"
rather than "cannot open anymore tables". In my experience, the error
"cannot open anymore tables" has been encountered fairly quickly when a
form or combination of forms is open.
However, sometimes you can open all the forms, no problem. Then you
start "doing things", running OnCurrent events, afterupdate events,
etc., and open databases start accruing, such that error 3048 does not
happen at first, but only after some use. In the case of the class
issue, I would open the form in question, workspace had 7 databases.
Close the form, now 6. Open the form again, now 13, close the form, now
12 left open. 6 left in memory each time, open the form often enough and
Err 3048 occured.
Lyle Fairfield
news:29b70902.03060...@posting.google.com:
> 1. I handle any DAO code very carefully--use a single global database
> variable, never set or unset a recordset in error, etc
About 99.99% of the time when someone (including me) says this, there’s an
error in the code.
--
Lyle
Lyle Fairfield
> 1. Why does a new database get opened every time a bound form gets
> opened? Is this so that the forms can be discretely updated?
It’s not every time.
When it does happen it’s usually because the person who designed the form or
concomitant objects made an error.
--
Lyle
downwitch
Well, thanks for all the feedback. I think some of it's pretty
useful, but it gives me great pleasure to say that the most helpful
response so far has been TR's code, because it allowed me to see
something pretty unusual, and it has to do with RecordsetClones.
This is what I've found: My forms themselves are not causing any
problems (and I didn't think they were, thank you very much.) Nor, in
general, is my code. BUT when I would set a recordset clone to a
subform, so that I could iterate through its records and figure
something out, then destroy that clone reference, the workspace (1)
creates a new database, (2) populates it with the subform's recordset
and the clone, and (3) doesn't destroy it, even when I close the clone
recordset and set the object to nothing. In fact, it leaves one of
the two recordsets in memory. If I set no clone, I get no extra
created database. Period.
This makes absolutely no sense; just because I use RecordsetClone, I
shouldn't have to pay for it--in fact, it should work *better* than
setting up a whole new rst, as it's already there. And as far as its
independence, well, it's a clone, not the underlying rst itself. It's
as if because I'm looking at it in Access, and looking at it in DAO,
Access says what the hell and just makes another db. Opening the
*same exact recordset* through the global CurrentDb variable I've set
adds no new database, naturally. And if Access is going to add a new
database every time I create a clone of a record's child records, and
not destroy that database, then I'm going to run out of databases long
before 2048, which is exactly what seems to have been happening--no
errors since I found this out, and started cleaning it up. (We're
basically talking about 5 or 6 clone references here and there; it's
not like I did it standard-practice.)
Also of note: setting that global variable to CurrentDb creates a
"second" database; setting it to DBEngine(0)(0) does not, but of
course the collections refresh more slowly, so it doesn't help. The
problem was *not* with the various recordsets (I said configuration,
but I should have said lookups, really--I do use a single config
recordset, and it's one of the 3 that's always loaded, the other two
being users and my form stack) I keep running in the background to
quickly return values--I haven't changed that at all.
Also of note: along the way, I've reset one of my forms to load
everything at record-retrieval time, as was suggested above. It does
indeed cut down slightly on the number of recordsets floating around,
and therefore (obviously) on the number of table handles, which I now
understand a little better. BUT it has also tripled the time it takes
to retrieve a form's record and its various dependents. I switched
back and forth between the two methods, time-testing all the while,
and the same data pulling from simple requery requests came up in
about 0.8 seconds, while assigning the row- and recordsource
properties has slowed it to about 2 seconds.
Anyway, I don't think that the various things suggested for rewriting
rowsources and using combo event properties are such hot ideas.
Writing a callback function is pretty easy, and they run very fast
indeed; I can't possibly imagine loading up stuff to valuelist
strings, testing, and maybe returning SQL instead running faster.
If you'd like to test the clone thing, here's a test procedure:
Public Sub TestClone()
Dim rst As DAO.Recordset
Set rst = Forms!MyForm!MySfrm.Form.RecordsetClone
rst.Close
Set rst = Nothing
End Sub
Run the modified version of TR's code pasted below before and after,
and you'll see what I see, unless we're running parallel-universe
versions of Access 2K.
Public Function CountDatabasesOpen(Optional ByVal pblePrintRSTNames As
Boolean = False)
Dim wks As Workspace
Dim rstTest As DAO.Recordset
Dim intDBCnt As Integer
Dim intRstCnt As Integer
Dim intRecCnt As Integer
Dim i As Integer
Dim j As Integer
Dim strResult As String
Set wks = DBEngine.Workspaces(0)
intDBCnt = wks.Databases.Count
strResult = "Total Databases: " & intDBCnt
For i = 0 To intDBCnt - 1
intRstCnt = wks.Databases(i).Recordsets.Count
strResult = strResult & vbNewLine & "DB(" & i & ") Recordsets:
" & intRstCnt
If pblePrintRSTNames = True Then
For j = 0 To intRstCnt - 1
Set rstTest = wks.Databases(i).Recordsets(j).Clone
With rstTest
If .RecordCount > 0 Then
.MoveLast
intRecCnt = .RecordCount
Else
intRecCnt = 0
End If
uStringAddValueToList strResult, "---- " & .Name
& " - " & intRecCnt & " records", vbNewLine
.Close
End With
Set rstTest = Nothing
Next j
End If
Next i
Debug.Print strResult
Set wks = Nothing
End Function
TR
limit, but seems to me it was low, less than 256? Your experience was
similar to mine, an eye opener in terms of watching objects instantiate,
then not go away when you thought they should. Although in my case it was
due to my code, and not realizing classes held in a collection will not
terminate even when the collection goes out of scope.
But, I have not been able to duplicate your observation. I do see the db
count increase as you noted, but the db count decrements once the form is
closed. Also, repeating the TestClone sub while the form is open does not
create additional dbs beyond the first (seems to use just one for the
form's clone). Could there have been something more going on in your code,
or the way the form was setup (Ijust used a very simple form/subform)?
Any recursive coding?
I'll try it some more, but at present cannot duplicate in A2002 or A97.
Maybe its an A2000 thing?
David W. Fenton
<3EDE4D42.F0A9F353@Mind_NoSpam_spring.com>:
>"David W. Fenton" wrote:
>
>> t_NoSpam_redick@Mind_NoSpam_spring.com (TR) wrote in
>> <3EDE056B.D32AA9BB@Mind_NoSpam_spring.com>:
>>
>> >I have run into this problem as well. Here is a function that
>> >enumerates databases and recordsets open.
>>
>> That won't tell you anything at all about the real number of
>> table handles in use.
>
>True, it does not.
>But it may be worthwhile confirming that he does not have dao open
>database issues, since the error was "cannot open anymore
>databases" rather than "cannot open anymore tables". In my
>experience, the error "cannot open anymore tables" has been
>encountered fairly quickly when a form or combination of forms is
>open. . . .
Er, I've never encountered "cannot open any more tables," only
"cannot open any more databases," which was the symptom of hitting
the table handles ceiling. True, the error message is wrong, as
it's tables that can't be opened, but that's the error message I
got.
> . . . However, sometimes you can open all the forms, no problem.
>Then you start "doing things", running OnCurrent events,
>afterupdate events, etc., and open databases start accruing, such
>that error 3048 does not happen at first, but only after some use.
>In the case of the class issue, I would open the form in question,
>workspace had 7 databases. Close the form, now 6. Open the form
>again, now 13, close the form, now 12 left open. 6 left in memory
>each time, open the form often enough and Err 3048 occured.
If table handles are the issue, number of open databases has
nothing to do with it, so examining it will help you none at all
with solving the problem.
David W. Fenton
>Public Sub TestClone()
> Dim rst As DAO.Recordset
> Set rst = Forms!MyForm!MySfrm.Form.RecordsetClone
> rst.Close
> Set rst = Nothing
>End Sub
Why not simply do this:
Dim frm As Form
Set frm = Forms!MyForm!MySfrm.Form
With frm.RecordsetClone
.FindFirst "whatever}
[do your thing with the RecordsetClone]
End With
Set frm = Nothing
And if you're frequently mucking around with a subform like that,
just have a module level form variable for it and use that
throughout your code (just be sure to explicitly close it in the
main form's UnLoad event).
The issue is this:
Why make a copy of the RecordsetClone when you can use it directly?
Indeed, I've never used recordset variables to deal with a
RecordsetClone, and have *still* run into table handles problems.
Have others used the recordset variable for working with
RecordsetClones on a regular basis? If so, why?
First off, the recordset you want to work with already exists. Why
would you need a different variable to work with it?
I guess the expected behavior is that the setting it would give you
a handle to the original RecordsetClone, not a new recordset based
on it, but it seems that it does not?
In any event, I never saw too much difficulty with dealing with the
thing directly. Why would you clone a clone?
David W. Fenton
>Anyway, I don't think that the various things suggested for
>rewriting rowsources and using combo event properties are such hot
>ideas. Writing a callback function is pretty easy, and they run
>very fast indeed; I can't possibly imagine loading up stuff to
>valuelist strings, testing, and maybe returning SQL instead
>running faster.
I wasn't suggesting it would run faster, only that it would reduce
the number of table handles simultaneously in use.
The advantage of what I described was that one function could
handle all your combo boxes, whereas for callback functions, you
need a different one for each, unless you somehow write a modular
one that gets the recordset it returns from via some structure
outside the callback function. You'd then need the same number of
columns and the same field names, etc., so it still wouldn't work
very well.
Also, you wouldn't be gaining a reduction in table handles, unless
your callback was pulling its data from an array that was
initialized from a recordset and then the recordset cleared.
My solution of loading value lists where possible might slightly
slow down the form's load process, but once the form was loaded,
the combo boxes would all be much faster, as callbacks are much
slower than value lists or SQL strings for lists of even moderate
length (even a couple 100 items).
I'm glad your problem was as easily identified as it was, but you
were using practices with the RecordsetClone that don't really have
any justification, so far as I can see. And the 20-odd lookup
recordsets are also something that look fishy to me, even if they
didn't have anything to do with the apparent cause of your problem.
TR
I wasn't arguing, or even disagreeing with you. If table handles are
the problem, then looking at the databases won't help. IF table handles
are the problem. I just didn't think that table handles should/could be
conclusively identified as the issue, until the database count had been
verified. We've had different experiences with err 3048 I guess, for me
its been encountered with databases left open, due to poor cleanup,
collected objects, or runaway recursion.