If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
cannot open any more databases...
I understand this error is due to having too many tables being accessed. I
ran into it today trying to clean up some bad design - a major lookup table storing the text value of a field rather than the ID. I was burned when I had to alter the name of one of the look up records but didn't trace all places it was used. Having the ID number stored instead would have avoided that... So now I get the "cannot open..." error when I run a process that runs 4 queries. Actually the process runs fine if I specify exporting to Excel, as each query is opened, output, and closed in that case. But sometimes it was useful to just run the queries by themselves. This is where I run into the error. So, is there a way around it? I tried to condense a bunch of the joins into another query, but still happened, so I think that this layering approach still triggers it. Also, is there any rule of thumb that would indicate when one might encounter this issue? TIA |
#2
|
|||
|
|||
On Mon, 22 Aug 2005 12:08:05 -0700, Ed Ardzinski
wrote: I understand this error is due to having too many tables being accessed. I ran into it today trying to clean up some bad design - a major lookup table storing the text value of a field rather than the ID. Um? That's often a GOOD idea. I was burned when I had to alter the name of one of the look up records but didn't trace all places it was used. Having the ID number stored instead would have avoided that... True... but so would setting Cascade Updates on all the relationships. So now I get the "cannot open..." error when I run a process that runs 4 queries. Actually the process runs fine if I specify exporting to Excel, as each query is opened, output, and closed in that case. But sometimes it was useful to just run the queries by themselves. This is where I run into the error. So, is there a way around it? I tried to condense a bunch of the joins into another query, but still happened, so I think that this layering approach still triggers it. Also, is there any rule of thumb that would indicate when one might encounter this issue? Could you give us a little help here? What is it that you're trying to run? Could you post the SQL of a typical one of the queries, and perhaps the VBA code you're using? If you're executing multiple queries in succession, you should close each one after opening it, and deinstantiate it. John W. Vinson[MVP] |
#3
|
|||
|
|||
Thanks for the reply, John. A little more...
Could you give us a little help here? What is it that you're trying to run? Could you post the SQL of a typical one of the queries, and perhaps the VBA code you're using? If you're executing multiple queries in succession, you should close each one after opening it, and deinstantiate it. Yes, when I do that everything runs as expected. I had sometimes found leaving the 4 datasets open useful, especially in the earlier stages of development. The report is a very large spreadsheet that nobody knew how to maintain. So I developed some VBA Excel code to take these 4 datasets and create an accurate and complete Excel file. Three of the queries are pretty simple, but now with fixing more of the look up tables but they are accessing more and more tables. The major export query is a bear... I have YTD and monthly versions of 6 different metric categories for our data (basically call data), a YTD and monthly "framework" query to coordinate the monthly and YTD data for all our divisions, and summary queries that join all the data together. Finally a union query takes the monthly and YTD queries and creates a single set of output data. A sample of the SQL in one of the underlying queries is such: SELECT Report_Products.Reported_Product, [Product Type].Product, qryGrevDataModLevel.[Health Plan], ReportTypes.ReportTypeDesc, qryGrevDataModLevel.NewLevel, Format([RecUHC],"yyyy-mm") AS [yyyy-mm], Count(qryGrevDataModLevel.Key) AS RecdCases FROM ((ReportTypes RIGHT JOIN ((qryGrevDataModLevel LEFT JOIN [Product Type] ON qryGrevDataModLevel.MbrType = [Product Type].Product) LEFT JOIN [Case Level] ON qryGrevDataModLevel.NewLevel = [Case Level].Level) ON ReportTypes.ReportTypeID = [Case Level].ReportTypeID) LEFT JOIN Report_Products ON [Product Type].RepProdID = Report_Products.ID) INNER JOIN Source ON qryGrevDataModLevel.SourceID = Source.ID WHERE (((qryGrevDataModLevel.RecUHC) Between [Forms]![frmRunExportQueries]![txtStartDate] And [Forms]![frmRunExportQueries]![txtEndDate]) AND ((Source.Complaint_Type) Like IIf([Report_Products].[Reported_Product]="EVERCARE MEDICAID" Or ([GrevApplData].[SiteID]=37 And [Product]="EVERCARE"),"Member","*") Or (Source.Complaint_Type) Like IIf([Report_Products].[Reported_Product]="EVERCARE MEDICAID" Or ([GrevApplData].[SiteID]=37 And [Product]="EVERCARE"),"Other","*"))) GROUP BY Report_Products.Reported_Product, [Product Type].Product, qryGrevDataModLevel.[Health Plan], ReportTypes.ReportTypeDesc, qryGrevDataModLevel.NewLevel, Format([RecUHC],"yyyy-mm"); There are still some major fields that have yet to be converted to the more manageable ID columns...when I cam aboard the desire was to "get it working"...not make it pretty. Supposedly a "new" system is being developed at the corporate HQ, but I suspect it will be a while before I see it! It's not really a problem to not have the user interface be able to open all 4 of these queries at once. If I have to look at one of them by themselves am I know how to do it. I was just wondering if there was any feel for when this issue crops up. I've seen it before, and worked around it...but it seems that it's related to the number of tables you have open at once, and the "better" design of storing an ID rather than a Text string for these major data fields seems to be leading at some point to this wall. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Exclusive Open Mode for Databases | codeman | General Discussion | 0 | August 22nd, 2005 12:53 PM |
Cannot open any more databases. (Error 3048) | rawiebe | Setting Up & Running Reports | 1 | May 25th, 2005 01:07 AM |
Can't open 2 databases at once | Clark | General Discussion | 1 | December 20th, 2004 02:28 AM |
Sudden database corruption of multiple databases. | Karen | General Discussion | 4 | December 15th, 2004 07:35 AM |
Can not open any more databases | Jeff C. | General Discussion | 2 | September 13th, 2004 10:20 PM |