asked on

Max number of stacked queries?

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

jadedata

Hey LoveToSpod!

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

regards
Jack

ASKER CERTIFIED SOLUTION

jadedata

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Poppekop

if it is a yes/no field, try putting condition to -1 instead of true (just a wild guess)

SOLUTION

jpolin1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jadedata

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...)

LoveToSpod

ASKER

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

jadedata

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"