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
Solved

Max number of stacked queries?

Posted on 2003-11-03
7
434 Views
Last Modified: 2009-07-29
Hi,

I have about six queries stacked in line. i.e. queries within queries...

Is there a maximum number of stacked queries allowed?

I just tried to add another query to the stack, and a simple answer seems un-obtainable. It is a three column query where on of the columns is a tickbox. When I set the criteria on the tickbox column to true, no records are returned. If I clear the criteria, there are records with both true and false returned values from the tickbox column.
This makes me beleive MS Access gives up when the queries are stacked so high.

Any help apprec...

LoveToSpod
0
Comment
Question by:LoveToSpod
7 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9671514
Hey LoveToSpod!

 as Long as the number of total fields (Tables * Field) does not exceed 255 you will be within the limits.

regards
Jack
0
 
LVL 32

Accepted Solution

by:
jadedata earned 400 total points
ID: 9671538
There is also the point when Access can no longer track the lowest level query in the stack space, which is a less calculable value.

You need to look at combining some of the lower level queries or switch to some temporary tables in order to make the data more static at the time the upper level queries try to evaluate the data.
0
 
LVL 4

Expert Comment

by:Poppekop
ID: 9671631
if it is a yes/no field, try putting condition to -1 instead of true (just a wild guess)
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 5

Assisted Solution

by:jpolin1
jpolin1 earned 100 total points
ID: 9671649
I would stay away from the stacked queries anyway. It is hard for someone else to figure
out what you were trying to return from the DB.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9671916
I agree with jpolin1 here.  Stacked queries, UNLESS written and used by VBA code can be really ugly to iron out later if there are additional issues or change requests that have an impact on these queries.  Even for you as the developer, 3 months later.... (when the memory starts to go...)
0
 

Author Comment

by:LoveToSpod
ID: 9680116
Making Temporary tables is a good way of speeding things up, but I find inflates the size of the application requiring it to be compacted regularly. Jack, when you say temp taboes, are you talking recordsets? If so, I will need to brush up a little. Do recordsets inflate the size of the application too?

Thanks for your advices.

LoveToSpod
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9680308
If you push those temp tables into a temp mdb you can throw them away with the bath water when the operation is completed

   dbengine.createdatabase("c:\folder\tempjunk.mdb")
   set dbTemp = opendatabase("c:\folder\tempjunk.mdb")
   ssql = "SELECT * INTO ["c:\folder\tempjunk.mdb"].[TempTable] FROM TableName WHERE ([This] = 'That');"
   currentdb.execute ssql, dbfailonerror

     'Your temp data is now in a "throw-away" temp container.
     'Perform ops on this data here
     'When done,....

   kill "c:\folder\tempjunk.mdb"

0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 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