Not enough space on temporary disk - why not?

[Guest]

I'm trying to run a query, but getting this error:

"Not enough space on temporary disk."

I don't understand why. I have 5+ gigs on my C: drive, and 18+ gigs on my
D: drive. I've looked this up in help, and found that this refers to my
"TEMP" variable in DOS. Mine is on my C: drive, currently using just over 2
gigs (2,097,152 KB), and leaving me the 5+ gigs still available. So what's
the problem? I do know that the .mdb limit is (or at least *was*) 2gb. But
this is a "temp" file, not an mdb.

What am I missing?

Thanks in advance,

Tom

 

[Guest]

Please post the SQL of the query and the approximate number of records in any
tables involved.

Also have you compacted and repaired your database lately? How big is it
before and after compacting?

 

[Tcs]

Please post the SQL of the query and the approximate number of records in any
tables involved.

Also have you compacted and repaired your database lately? How big is it
before and after compacting?

Size before:
------------
code.mdb - 1,338,924 KB
data.mdb - 94,360 KB

Size after:
-----------
code.mdb - 792,472 KB
data.mdb - 69,996 KB

------------
SELECT DISTINCT
tblCXLIB_UT420AP.UTCSID AS [Cust ID],
tblCXLIB_UT420AP.UTLCID AS [Loc ID],
tblCXLIB_UT420AP.UTSVC AS Svc,
tblCXLIB_UT420AP.UTRBCM AS BRComp,
tblCXLIB_UT420AP.UTCHGT AS ChgType,
tblCXLIB_UT420AP.UTTTYP AS TransType,
tblCXLIB_UT420AP.UTTDSC AS TransDesc,
tblCXLIB_UT420AP.UTTMM AS [Trans MM],
tblCXLIB_UT420AP.UTTDD AS [Trans DD],
tblCXLIB_UT420AP.UTTYY AS [Trans YY],
tblCXLIB_UT420AP.UTTAMT AS [Trans Amt],
tblCXLIB_UT420AP.UTUNPD AS [Amt Unpaid],
tblCXLIB_UT420AP.UTRCCD AS [Rec Code],
tblCXLIB_UT420AP.UTAGE AS [Age Code],
tblCXLIB_UT420AP.UTEXTD AS [ExPmt Plan],
tblCXLIB_UT420AP.UTPPRI AS PostAuth,
tblCXLIB_UT420AP.UTBILM AS [Bill MM],
tblCXLIB_UT420AP.UTBILD AS [Bill DD],
tblCXLIB_UT420AP.UTBILY AS [Bill YY],
tblCXLIB_UT420AP.UTRJUR AS Juris,
tblCXLIB_UT420AP.UTRCLS AS [Rate Class],
tblCXLIB_UT420AP.UTRIO AS [In-Out],
tblCXLIB_UT420AP.UTRMSZ AS [Mtr Size],
tblCXLIB_UT420AP.UTREDY AS [Effect YY],
tblCXLIB_UT420AP.UTREDM AS [Effect MM],
tblCXLIB_UT420AP.UTREDD AS [Effect DD],
tblCXLIB_UT420AP.UTRSW AS Season,
tblCXLIB_UT420AP.UTPEMM AS [Period MM],
tblCXLIB_UT420AP.UTPEYY AS [Period YY],
tblCXLIB_UT420AP.UTGREF AS [Gen Refer#],
tblCXLIB_UT420AP.UTCHCN AS [Chrgd Cons],
tblCXLIB_UT420AP.UTADCH AS [Adj Chg Typ],
tblCXLIB_UT420AP.UTBBFL AS [Budget Flag],
tblCXLIB_UT420AP.UTDMCN AS [Dmd Consump],
tblCXLIB_UT420AP.UTDPNO AS [Unique Dep#],
tblCXLIB_UT420AP.UTUSER AS [User Name],
Now() AS [Timestamp]

INTO
tblAnnRptFY05a_EL_cons IN
'D:\Projects\AS400\UtilityBilling\As400_Finance_data.mdb'

FROM
tblAnnRptFY05a_EL LEFT JOIN tblCXLIB_UT420AP ON
(tblAnnRptFY05a_EL.[Cust ID] = tblCXLIB_UT420AP.UTCSID) AND
(tblAnnRptFY05a_EL.[Loc ID] = tblCXLIB_UT420AP.UTLCID);
------------

I'm querying a DB2 db on an AS/400. Something I do all the time. Admittedly,
in this case I'm going to get back a lot of data, a year's worth of trans for
all active customers. (Roughly 18k customers * 8 trans/cust (approx) * 12
months.)

 

[Guest]

Looks like the tables are properly joined as seen below so that there isn't
an outright Cartisian product. However you really don't know if there are
multiple joins between the fields unless one side is the primary key. You
might have a mini cartisian product happening.

tblAnnRptFY05a_EL LEFT JOIN tblCXLIB_UT420AP ON
(tblAnnRptFY05a_EL.[Cust ID] = tblCXLIB_UT420AP.UTCSID) AND
(tblAnnRptFY05a_EL.[Loc ID] = tblCXLIB_UT420AP.UTLCID);

Next you are doing a DISTINCT which will take a lot of temporary space to
weed out any duplicate records. It might be best to first create a make table
query without the DISTINCT then insert the records from there.

Lastly I don't know if a DB2 db on an AS/400 supports it, but a Pass-through
query would be a lot better if possible. Instead of bringing all the data
into Access, let the DB2 database do most of the work for you. In the case of
other databases, like SQL Server, pass-thru queries are much more efficient.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Please post the SQL of the query and the approximate number of records in any
tables involved.

Also have you compacted and repaired your database lately? How big is it
before and after compacting?

Size before:
------------
code.mdb - 1,338,924 KB
data.mdb - 94,360 KB

Size after:
-----------
code.mdb - 792,472 KB
data.mdb - 69,996 KB

------------
SELECT DISTINCT
tblCXLIB_UT420AP.UTCSID AS [Cust ID],
tblCXLIB_UT420AP.UTLCID AS [Loc ID],
tblCXLIB_UT420AP.UTSVC AS Svc,
tblCXLIB_UT420AP.UTRBCM AS BRComp,
tblCXLIB_UT420AP.UTCHGT AS ChgType,
tblCXLIB_UT420AP.UTTTYP AS TransType,
tblCXLIB_UT420AP.UTTDSC AS TransDesc,
tblCXLIB_UT420AP.UTTMM AS [Trans MM],
tblCXLIB_UT420AP.UTTDD AS [Trans DD],
tblCXLIB_UT420AP.UTTYY AS [Trans YY],
tblCXLIB_UT420AP.UTTAMT AS [Trans Amt],
tblCXLIB_UT420AP.UTUNPD AS [Amt Unpaid],
tblCXLIB_UT420AP.UTRCCD AS [Rec Code],
tblCXLIB_UT420AP.UTAGE AS [Age Code],
tblCXLIB_UT420AP.UTEXTD AS [ExPmt Plan],
tblCXLIB_UT420AP.UTPPRI AS PostAuth,
tblCXLIB_UT420AP.UTBILM AS [Bill MM],
tblCXLIB_UT420AP.UTBILD AS [Bill DD],
tblCXLIB_UT420AP.UTBILY AS [Bill YY],
tblCXLIB_UT420AP.UTRJUR AS Juris,
tblCXLIB_UT420AP.UTRCLS AS [Rate Class],
tblCXLIB_UT420AP.UTRIO AS [In-Out],
tblCXLIB_UT420AP.UTRMSZ AS [Mtr Size],
tblCXLIB_UT420AP.UTREDY AS [Effect YY],
tblCXLIB_UT420AP.UTREDM AS [Effect MM],
tblCXLIB_UT420AP.UTREDD AS [Effect DD],
tblCXLIB_UT420AP.UTRSW AS Season,
tblCXLIB_UT420AP.UTPEMM AS [Period MM],
tblCXLIB_UT420AP.UTPEYY AS [Period YY],
tblCXLIB_UT420AP.UTGREF AS [Gen Refer#],
tblCXLIB_UT420AP.UTCHCN AS [Chrgd Cons],
tblCXLIB_UT420AP.UTADCH AS [Adj Chg Typ],
tblCXLIB_UT420AP.UTBBFL AS [Budget Flag],
tblCXLIB_UT420AP.UTDMCN AS [Dmd Consump],
tblCXLIB_UT420AP.UTDPNO AS [Unique Dep#],
tblCXLIB_UT420AP.UTUSER AS [User Name],
Now() AS [Timestamp]

INTO
tblAnnRptFY05a_EL_cons IN
'D:\Projects\AS400\UtilityBilling\As400_Finance_data.mdb'

FROM
tblAnnRptFY05a_EL LEFT JOIN tblCXLIB_UT420AP ON
(tblAnnRptFY05a_EL.[Cust ID] = tblCXLIB_UT420AP.UTCSID) AND
(tblAnnRptFY05a_EL.[Loc ID] = tblCXLIB_UT420AP.UTLCID);
------------

I'm querying a DB2 db on an AS/400. Something I do all the time. Admittedly,
in this case I'm going to get back a lot of data, a year's worth of trans for
all active customers. (Roughly 18k customers * 8 trans/cust (approx) * 12
months.)