John Colby
jwcolby at ColbyConsulting.com
Thu Apr 20 14:42:08 CDT 2006
?but ADO is the bomb What does this mean? Bad or good? John W. Colby www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Long, Karen S. Sent: Thursday, April 20, 2006 3:33 PM To: Access Developers discussion and problem solving Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com