[AccessD] Query Results Too Big?

Do you have more than one drive?

I have had this kind of message when I was working on my D: drive with 
sufficient space available but my C: drive was too full. Seems as this 
temporary work space was on the C:  drive - might have been in C:\Temp, not 
sure though. I did a search for files modified in the last day and found the 
files it was creating. I'm not sure they were MDB files, seems as though 
they were a different extension.  Things get squirrelly when you get under 
10% free on the main OS drive.

I often do queries with as many as 6 million rows in a table and a couple a 
hundred thousand in another. I would also wonder if you are somehow getting 
some kind of a cartesan product type of a result.....Check your joins and 
make sure your not joining something where they all join to everything in 
the other table.

PS - sorriy if this posts multiple times - been getting "server to busy" 
messages from HOTMAIL when I try to send it.....seems as if they didn't go 
through so I kept trying - this is send attempt #3

Gary Kjos
garykjos at hotmail.com



>From: "Rocky Smolin - Beach Access Software" <bchacc at san.rr.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Query Results Too Big?
>Date: Thu, 4 Sep 2003 08:22:51 -0700
>
>Jim:
>
>I've got 3 1/2 GB free.  That enough?  Must be if the 2GB Access db limit 
>is any indication.  If the temp tables holding the query results are inside 
>the db then quite possibly is hitting the Access limit without using up all 
>the hd space?  It's a select query.
>
>Regards,
>
>Rocky Smolin
>Beach Access Software
>
>   ----- Original Message -----
>   From: Jim Dettman
>   To: Access Developers discussion and problem solving
>   Sent: Thursday, September 04, 2003 7:51 AM
>   Subject: RE: [AccessD] Query Results Too Big?
>
>
>   Rocky,
>
>   <<
>   I have a query which is the record source for a report which eventually 
>craps out with the message 'Not enough space on temporary disk' or some 
>such.  The query is joining 2 tables which each have over 2 million 
>records.
>   >>
>
>    What type of query is it?  If it's creating a snapshot of the data, 
>then you could easily run out of disk space.  How much free space do you 
>have on your drive?  I've done queries with 1,500,000 records without 
>problems.
>
>   <<
>   Is there a limit to how big a query result Access can generate?  Is 
>there a setting to increase the limit?
>   >>
>
>     The only one I'm aware of is the 2048 table reference limit and the 
>fact that it must "compile" (Microsoft's term not mine) into a memory space 
>slightly larger then 64K (they have never said how much larger).
>
>   Jim Dettman
>   President,
>   Online Computer Services of WNY, Inc.
>   (315) 699-3443
>   jimdettman at earthlink.net
>     -----Original Message-----
>     From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin - 
>Beach Access Software
>     Sent: Thursday, September 04, 2003 10:39 AM
>     To: AccessD at databaseadvisors.com
>     Subject: [AccessD] Query Results Too Big?
>
>
>     Dear List:
>
>     I have a query which is the record source for a report which 
>eventually craps out with the message 'Not enough space on temporary disk' 
>or some such.  The query is joining 2 tables which each have over 2 million 
>records.
>
>     Is there a limit to how big a query result Access can generate?  Is 
>there a setting to increase the limit?
>
>     I think I may have reached the limit of Access and am looking now for 
>other ways to give the user what they want.  Short of going to MSDE.
>
>     TIA
>
>     Rocky Smolin
>     Beach Access Software
>

_________________________________________________________________
Get 10MB of e-mail storage! Sign up for Hotmail Extra Storage.  
http://join.msn.com/?PAGE=features/es