Ms Access Error Cannot Open Any More Databases
Contents |
Posters Today's Posts Search Community Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go access 2013 cannot open any more databases to Page... Thread Tools Rating: Display Modes 02-13-2007, 02:14 AM
Error 3048 Cannot Open Any More Databases
#1 Dwight Registered User Join Date: Mar 2003 Posts: 168 Thanks: 0 Thanked 0 Times in 0 Posts Error 3048: Cannot open any more databases My database sometimes produces Error 3048: Cannot open any more databases. After researching this on the Internet I now know that it is because Access has an internal limit of 255 simultaneous connections to Jet. A “connection” includes things like combo boxes, recordsets, queries, etc…..and these add up faster than one might think. I believe the culprit may be a particularly complex report my users frequently run. It is composed of several subreports and each subreport is based on several nested queries. I used nested queries a lot throughout my database. And sometimes these nested queries contain subqueries further increasing their complexity. For example, the complex report probably requires at least 30 queries to run to produce the output. My questions are: 1) Are all 30 “query connections” open while the report is open? Or are only the final queries in the nested series open? 2) I could create temp tables and append the data to the table and then pull it out with a query and base the report on this. Would this reduce the number of connections because only the queries pulling the data from the tables would be “open”? (Generally, temp tables seem to be frowned upon by the experts on this forum) Sometimes I do use recordsets and I have also gone back through to make sure that each time I open a recordset it gets closed e.g., rst.close. Thanks. Dwight View Public Profile Find More Posts by Dwight 02-13-2007, 02:41 AM #2 KenHigg Registered User Join Date: Jun 2004 Posts: 13,295 Thanks: 7 Thanked 155 Times in 129 Posts a. I'm not sure the number of queries running is producing the error, but my quess is that you're on the right track. b. D
soon) Ruby (coming soon) Getting Started Code Samples Resources Patterns and Practices App Registration Tool Events Podcasts Training API Sandbox Videos Documentation Office Add-ins Office Add-in Availability Office Add-ins Changelog Microsoft Graph API Office 365 Connectors Office 365 REST APIs SharePoint Add-ins Office UI Fabric Submit to the Office Store All Documentation https://www.yammer.com/ http://feeds.feedburner.com/office/fmNx Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: cannot open any more databases Microsoft Office for Developers > Access for Developers Question 0 Sign in to vote I have an application that I'm working on. It is split, both front and back are in the same folder on my local drive. It is in development still, so no one else is accessing http://www.access-programmers.co.uk/forums/showthread.php?t=122973 it. I am working on a report that has three subreports. All of the data comes from the same backend. Each subreport and the main report have queries as their data source. Each query is different, but the two of the subreport's queries use a common query as one of the sources. When I try to preview the report, I get the message that is in a loop which says that I cannot open any more databases. There are only two databases https://social.msdn.microsoft.com/Forums/office/en-US/9993d229-8a00-4a59-a796-dfa2dad505bc/cannot-open-any-more-databases?forum=accessdev open - the front-end and the back-end. For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8. Wednesday, May 22, 2013 4:53 PM Reply | Quote Answers 0 Sign in to vote I sent you an email with the zipped folder attached. thank you for taking time to look at it.. I have sent you a database with a revised version of the report that uses no subreports, which works in both Report View and Print Preview. Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html Marked as answer by -suzyQ Friday, May 31, 2013 5:12 PM Friday, May 31, 2013 4:55 PM Reply | Quote All replies 0 Sign in to vote Hi Suzy, When you run the underlying Query's one at time do you get the same error? It could be that it is just too complex and you might need to use temp tables for the Report. Chris Ward Edited by KCDW Wednesday, May 22, 2013 6:21 PM Wednesday, May 22, 2013 6:18 PM Reply | Quote 0 Sign in to vote Hi Suzy, When you run the underlying Query's one at time do you get the same error? It could be that it is just too complex and you might
I understand it is a best practice in a multi-user environment to split your database into a front-end and a back-end. However, from my experience and from many others I've seen online, doing so makes you more vulnerable to the "Cannot open any http://www.pcreview.co.uk/threads/cannot-open-any-more-databases-and-splitting-databases.3698326/ more databases" error due to reaching the Jet limit of table handles open. Evidently, opening a linked table is more expensive than a local one. For example, when I un-split my database, no other changes made, the same form opens without error. In my case the error has nothing to do with DLookup (which I don't use), or not closing recordsets in code, or too many query-sourced comboboxes. It has to do with splitting the database. I have not seen a satisfactory answer cannot open to this (short of moving to a different back-end database). I am looking at simplifying one of the queries I use a lot which joins ~10 tables. Although in the short term I think I don't have a choice but to NOT split the database. Eric ericgj, Dec 18, 2008 #1 Advertisements Tony Toews [MVP] Guest ericgj <> wrote: >I understand it is a best practice in a multi-user environment to split your >database into a front-end and a back-end. > cannot open any >However, from my experience and from many others I've seen online, doing so >makes you more vulnerable to the "Cannot open any more databases" error due >to reaching the Jet limit of table handles open. Evidently, opening a linked >table is more expensive than a local one. For example, when I un-split my >database, no other changes made, the same form opens without error. In my >case the error has nothing to do with DLookup (which I don't use), or not >closing recordsets in code, or too many query-sourced comboboxes. It has to >do with splitting the database. There is something interesting going on. I've only received this error occasionally, every few years or so. It's only happened when I had some very complex reports and many queries executing. >I have not seen a satisfactory answer to this (short of moving to a >different back-end database). I am looking at simplifying one of the queries >I use a lot which joins ~10 tables. Ten tables isn't a great deal for a query. Is this only just the one query? Is it run behind a report? Are there many subreports? Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Tony Toews [MVP], Dec 18, 2008 #2 Advertisements David W. Fenton Guest =?Utf-8?B?ZXJpY2dq?= <> wrote in news