[AccessD] Can't open any more databases

Converting the queries to code helps, shapshot mode is faster, but ADO
is the bomb. 


Karen S. Long
Programmer Analyst
EG&G Technical Services, Inc.
Pittsburgh, PA
Phone: 412-386-6649
Email: cyx5 at cdc.gov


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Thursday, April 20, 2006 3:06 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Can't open any more databases

I have a specific tabbed form in the Disability Insurance Specialists
database, which shows all claim info, i.e. all claim and child tables.
There are 19 tabs!  Most tabs contain subforms displaying data from the
tables child to Claim.  I use Just In Time subforms to only open the
subforms when a specific tab is clicked on (all driven automatically by
the framework BTW).

This works just fine.  The only issue that just started occurring is the
"can't open any more databases".  This is one of Access misleading error
messages, and appears to be caused by the fact that each object with a
data source such as forms (subforms), combos and list boxes all "open a
connection" to the BE which stays open as long as the object is open.  I
haven't (recently) counted how many such objects can be open at once but
the number is quite large.  I seem to remember from long ago when I was
investigating this, that it is something like 240.

One workaround that I am using is to unload the subform on a tab as the
person "tabs away".  Since the subform now closes, all such objects
contained in that form close and their "connection" is returned to the
pool of available connections.  This has a rather serious side effect of
slowing down the operation of the form when they go back to a specific
tab containing subforms, as the subform has to reload every time the tab
is clicked.

I would try to use ADO recordsets if I thought it would help.  Before I
do that though, does anyone know if changing the query behind a combo to
a snapshot would prevent this issue?  IOW, does a recordset being a
snapshot allow Access to close the connection (and does Access do so)?

The vast majority of these "open recordsets" are for combos, and if
changing to a snapshot would return the connection to the pool, then
this would go a long way to ameliorating this issue

John W. Colby
www.ColbyConsulting.com 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com