Solved

Cannot open more databases

Posted on 2014-04-25
9
1,819 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
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 15

Accepted Solution

by:
Ess Kay earned 125 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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 84
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 34

Assisted Solution

by:PatHartman
PatHartman earned 125 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now