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

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]

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.