Access Error Cannot Open Any More Databases
Contents |
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 ms access cannot open any more databases Changelog Microsoft Graph API Office 365 Connectors Office 365 REST APIs SharePoint microsoft access cannot open any more databases 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 access 2010 cannot open any more databases 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 access 2013 cannot open any more databases 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 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
Error 3048 Cannot Open Any More Databases
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 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
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 to Page...
Access Temp Tables
Thread Tools Rating: Display Modes 02-13-2007, 02:14 AM #1 Dwight Registered User cannot open any more tables 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. https://social.msdn.microsoft.com/Forums/office/en-US/9993d229-8a00-4a59-a796-dfa2dad505bc/cannot-open-any-more-databases?forum=accessdev 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 http://www.access-programmers.co.uk/forums/showthread.php?t=122973 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. Doing the temp table sounds like a good idea. c. With 30 queries, I would bet that you may have normalization issu
Forums Microsoft Access Why Access returns: Cannot open anymore databases Results 1 to 4 of 4 Why Access returns: Cannot open anymore databasesThis is a discussion on Why Access returns: Cannot open anymore databases within the Microsoft Access forums, part of the Question Forums category; I tried to http://www.mrexcel.com/forum/microsoft-access/264168-why-access-returns-cannot-open-anymore-databases.html run a series of queries on Access and the first 6 ones run smoothly but the 7th http://www.pcreview.co.uk/threads/cannot-open-any-more-databases-and-splitting-databases.3698326/ ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode May 31st, 2007,05:20 AM #1 mrchonginhk Board Regular Join Date Dec 2004 Posts 611 Why Access returns: Cannot open anymore databases I tried to run a series of queries on Access and cannot open the first 6 ones run smoothly but the 7th one gets this error:- "Cannot open anymore databases" How to deal with this ? Pls help Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote May 31st, 2007,05:32 AM #2 Andrew Fergus MrExcel MVP Join Date Sep 2004 Location New Zealand Posts 5,316 Hi Can you provide some more information? For instance : Is this a split database? Are the queries being run in VBA or from a form etc? Are the cannot open any 7 queries related to each other in some way, or are they independent? Regards Andrew Share Share this post on Digg Del.icio.us Technorati Twitter ~ >*()))><( I'd rather have a full bottle in front of me than a full frontal lobotomy. Reply With Quote Jun 2nd, 2007,01:40 AM #3 mrchonginhk Board Regular Join Date Dec 2004 Posts 611 No VBA, just SQL. 2 depends on 1, 3 depends on 2 etc Help .... Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Jun 2nd, 2007,07:35 PM #4 Andrew Fergus MrExcel MVP Join Date Sep 2004 Location New Zealand Posts 5,316 Hi A couple of things come to to mind. Either you have breached the limit for the number of tables for a query (which is 32) or you have breached the number of levels of nested queries (which is 50). If the 7th query is based on the first 6 queries, then if you add up the number of tables and/or queries, does the number exceed either 32 or 50? The number of tables can be easily breached where you have a whole lot of lookup tables (i.e. tables that store key values that you then use to populate another table). Can the queries be simplified in some way? Can the functionality in one query be pushed back into a previous query? I will probably regret asking you to post the details for each query but I think it is the possibly the number and complexity of the
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 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 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. > >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 an