Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cannot open more databases

Posted on 2014-04-25
9
Medium Priority
?
2,087 Views
Last Modified: 2014-04-25
I have a query that sometimes opens and sometimes doesn't. When it doesn't open, the warning box says "Cannot Open Any More Databases". I then have to close any other open form or query and it will run.

I believe it has to do with the number of queries it is running... it includes a lot of queries. Any easy solution to this? I believe it is my novice building skills that have so many queries involved. Not sure how to condense them though.

The query is below. Any help would be great.

SELECT DaySpecs.DaySpec, Avg(qryBookingDayswithYearAllAverages.AvgOfAvgOfSumOfExtendedPrice) AS AvgOfAvgOfAvgOfSumOfExtendedPrice, Avg(qryBookingDayswithYearAllAverages.AvgOfCountOfDAY) AS AvgOfAvgOfCountOfDAY, qryBookingTotalsAllSums.SumOfSumOfExtendedPrice, qryBookingTotalsAllSums.SumOfCountOfDAY, qryStatsforDailySalesReportFINAL.SumOfMessagesRecorded, qryStatsforDailySalesReportFINAL.SumOfTotalNewClients, qryStatsforDailySalesReportFINAL.SumOfBookedTrue, qryStatsforDailySalesReportFINAL.SumOfTalkedTo, qryStatsforDailySalesReportFINAL.SumOfFiveMinCallBack, qryReachOutSalesALL.SumOfCountOfCallMade, qryReachOutALL.SumOfCountOfReachOutSent
FROM ((((DaySpecs LEFT JOIN qryBookingDayswithYearAllAverages ON DaySpecs.DaySpec = qryBookingDayswithYearAllAverages.DateSpec) LEFT JOIN qryStatsforDailySalesReportFINAL ON DaySpecs.DaySpec = qryStatsforDailySalesReportFINAL.DateSpec) LEFT JOIN qryReachOutSalesALL ON DaySpecs.DaySpec = qryReachOutSalesALL.DateSpec) LEFT JOIN qryReachOutALL ON DaySpecs.DaySpec = qryReachOutALL.DateSpec) LEFT JOIN qryBookingTotalsAllSums ON DaySpecs.DaySpec = qryBookingTotalsAllSums.DateSpec
GROUP BY DaySpecs.DaySpec, qryBookingTotalsAllSums.SumOfSumOfExtendedPrice, qryBookingTotalsAllSums.SumOfCountOfDAY, qryStatsforDailySalesReportFINAL.SumOfMessagesRecorded, qryStatsforDailySalesReportFINAL.SumOfTotalNewClients, qryStatsforDailySalesReportFINAL.SumOfBookedTrue, qryStatsforDailySalesReportFINAL.SumOfTalkedTo, qryStatsforDailySalesReportFINAL.SumOfFiveMinCallBack, qryReachOutSalesALL.SumOfCountOfCallMade, qryReachOutALL.SumOfCountOfReachOutSent;

Open in new window

0
Comment
Question by:cansevin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 15

Accepted Solution

by:
Ess Kay earned 500 total points
ID: 40022718
you just answered your own question. MS ACCESS is made for small data. there are plenty of limits on almost everything, from haveing 50k row in a table, to possibly the ammount of databases open simultaneously.




You can try one of these:
1. Create temp tables and insert data you querried from database, then close the database connection, and use the temp tables instead

2. Migrate everything to sql server


code for temp tables are found here
http://accessexperts.com/blog/2011/07/20/use-temp-tables-in-your-code-for-quick-and-easy-analysis/
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40022740
from haveing 50k row in a table
That's wrong. Access does not have a limit to the number of rows, but rather a limit to the size of the database (much like SQL Server Express). You may be thinking of Excel, which has a row limit of around 65k ... but Access tables can have a LOT more than 50,000 rows. I saw one just yesterday with around 200,000 rows in a single table ...

Access does open many simultaneous connections, depending on what you're doing. The limit to the "number of open tables" is 2048, and you'd be surprised how quickly you can get there. An "open table" can be created with a query (and a single query can have several open tables), a combo, listbox, subform, report, etc etc.

And, it looks like you have a lot of summary queries going on. Often you're better off dumping that data into a temporary table in the local database, and querying from there. This can generally reduce the number of open tables.

See this link for specifications:
http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40022774
you just answered your own question. MS ACCESS is made for small data. there are plenty of limits on almost everything, from haveing 50k row in a table, to possibly the ammount of databases open simultaneously.

As Scott said, this is simply not true.   I've had close to a million records in a JET table without issue.

The issue your bumping into is because your building queries on top of queries.  At most, you should nest only a level or two.

The other thing that may lie at the heart of the problem is if you have not properly normalized your data.  If you have not done this, then it will be very inefficient to pull the information you need from the data, thus leading you to bump into limits more easily.

A good example of that is the 255 field limit in a table.  Often, many will take the "flat file" approach to Access tables when coming from Excel.   but Access is a relational database product and just doesn't work that way.

 As a result, you bump into the limit.  In a properly designed relational DB, it's rare to get beyond 50 or so fields in a single table.

I think at this point, given your questions to date and the problems your bumping into, it would be best for you to post a sample database and have the table structure reviewed first before you get too much farther with this app.

Jim.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:cansevin
ID: 40022775
Ok... thanks guys. I am unfamiliar with the process of temporary tables. Not sure how it would help. Ultimately this query is is attached to a report that has a button on a form. When the button is pushed, the report opens which runs the query. How would temporary tables reduce the number of open tables?

Thanks for your help! Much appreciated.
0
 
LVL 85
ID: 40022969
Temporary tables can help you to reduce the number of open tables, since a "multi-layered" query approach would be avoided. You would essentially dump the data from your bottom-layer queries into a table, and then just build a report based on that table. This generally comes at a performance cost, since it takes some time to fill tables and such. It's not a panacea for all ills, but instead should be something viewed as a possible workaround until you can get the real issues fixed.

That said, I agree with Jim - you obviously have some design issues (I think we've pointed this out to you previously), and it's becoming apparent that you'll have to fix those before you can move forward.
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40023023
sorry, you are correct guys, the 50k limit was over a decade ago

The hard limit is the 2 Gb Access database file size.



http://webcheatsheet.com/sql/access_specification.php
0
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 40023134
There has NEVER been a row count limit.  There is a record length limit however.  It is either 2,000 or 2k.  Database size has always dictated table size limits.

We can't tell from the query you posted just how many nesting levels you have. Can you count the queries/levels for us please.  I've had some complicated queries that have been nested 6 levels.  I don't know what the limit it because I haven't encountered it.  What else do you have open at the same time?  Since the error doesn't happen all the time, it may be simply that you actually have too many queries opened at the same time.

I rarely use temp tables since they cause bloat and Access treats queries and tables as interchangeable for most purposes.  When I do use temp tables it is usually because I have extensive reporting to do from the same summarized set of data.  For one bank, I created some very complex reports that used 2 years worth of transaction data.  The base data was 25 MILLION rows per year.  So rather than starting from 50 million every time, I summarized to about half a million per year as my basis and then sliced and diced from that point for the various reports.  This was also an excellent example of how Access lets the server do the heavy lifting.  I did have to bring down almost a million rows but I only did that because I didn't have permission to create temp tables on the server so I had to do the final processing locally with Jet.
0
 

Author Closing Comment

by:cansevin
ID: 40023426
Thank you all! For now, if I close my main form (which has a large number of subforms), it will work fine. That will do for the time being. Thanks for the knowledge!
0
 
LVL 75
ID: 40023440
The 'Accepted' solution contains a lot of incorrect information !
Just sayin ' ... not to mention the 'link' issue discussed elsewhere :-)

mx
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question