Error 3183 Not Enough Space on Temporary Disk

[KrispyData]

I am running a query that is based on 9 other queries and 2 tables. The
query runs for about 5 minutes then I get Error 3183. I confirmed that I do
have plenty of disk space available. I'm wondering if this is becuase the
query has to run through the 9 queries that is based on and it is a matter of
too much data? Should I turn the 9 queries into tables? Or, should I break
down the query?

I would post the SQL but it comes out to a full 8.5x11 page! My query is
probably not written as efficiently as it should be - I am still new to MS
Access.

Any suggestions will be greatly appreciated!

 

[Piet Linden]

I am running a query that is based on 9 other queries and 2 tables.  The
query runs for about 5 minutes then I get Error 3183.  I confirmed thatI do
have plenty of disk space available.  I'm wondering if this is becuase the
query has to run through the 9 queries that is based on and it is a matter of
too much data?  Should I turn the 9 queries into tables?  Or, should I break
down the query?  

I would post the SQL but it comes out to a full 8.5x11 page!  My query is
probably not written as efficiently as it should be - I am still new to MS
Access.

Any suggestions will be greatly appreciated!

"Application-defined or object-defined error"...

That tells me pretty much nothing. Post your SQL statement.
Do your other queries run if you run them individually?

 

[LloydBrown]

Whenever I have problems from complex queries like this, I chop it up in
pieces. Take some of the query structure and make it into a make table query.
Then attach the rest of the structure to the table you just made.
If you need the results to be updatable, make the table on a practice run
and set whatever primary key you need, then change the make table query to an
append query so the fields will connect. Create another delete query to empty
the target table.
When you need an update, run the delete query then the append query. This
way you don't ever change the target table's properties and your final query
connecting to the rest of the data can be updatable. You can get the same
results in code, but this is very easy. I would suggest numbering the queries
so you don't forget to delete then append to repopulate.

 

[KrispyData]

Hi Piet,

Each individual query does run successfully. Here is the SQL for the query
that gives me the 3183 error:

SELECT qryOCProfitFinalByClinicOCNetSales.[2007] AS OCNetSales2007,
qryOCProfitFinalByClinicOCNetSales.[2008] AS OCNetSales2008,
qryOCProfitFinalByClinicOCNetSales.[2009] AS OCNetSales2009,
qryOCProfitFinalByClinicOCNetSales.OC_NetSales_TTM, addreplist.[Dist Name],
addreplist.[Rep Name], qryOCProfitFinalByClinicOCNetSales.[Acct #],
qryOCProfitFinalByClinicOCNetSales.[Acct Name],
qryOCProfitFinalByClinicOCNetSales.[Corp #],
qryOCProfitFinalByClinicOCNetSales.Opened,
qryOCProfitFinalByClinicOCNetSales.[Status Code],
t_OCProfit_ANALYSIS_BY_CORP.Tier,
q_sc_Corp_TNF_Grade_LastPeriod.TNF_SalesPct,
q_sc_Corp_TNF_Grade_LastPeriod.Grade AS TNF_Grade,
q_sc_Corp_ASP_LastPeriod.OC_ASP, q_sc_Corp_ASP_LastPeriod.Grade AS GradeASP,
q_sc_Corp_ASP_Last12_s2.ASP AS ASP_Last12Mo,
q_sc_Corp_ASP_LastPeriod.ASP_OCAvg, q_sc_Corp_TNF_Grade_LastPeriod.TNF_OCavg,
t_OCProfit_ANALYSIS_BY_CORP.InvList,
t_OCProfit_ANALYSIS_BY_CORP.LastMonthInvWeeks AS InvWeeks,
t_OCProfit_ANALYSIS_BY_CORP.Grade_Inv, q_sc_Corp_PPAleadTime_Last.PPAlag AS
PPAlagLast, q_sc_Corp_PPAleadTime_Last.Grade AS PPAlagGrade,
q_sc_Corp_PPAleadTime_Last3.PPAlag AS PPAlagLast3, IIf([Clinic Payment
Table]![Field Tech]="DJO","Staffed Account - DJO",IIf([Clinic Payment
Table]![Field Tech]="DP","Staffed Account - Distributor","Non-staffed
Account")) AS FTech, q_sc_Corp_PPAleadTime_OCAvg.PPAlagOCavg,
q_sc_Corp_PctPPAsOver30.PctOver30, q_sc_Corp_PctPPAsOver30.Grade AS
PctOver30Grade, q_sc_Corp_CollectRate_Last_s2.Rate AS CollectionRate,
q_sc_Corp_CollectRate_Last_s2.CollectionGrade,
q_sc_Corp_HB_last4weeksGrade_2.HB, q_sc_Corp_HB_last4weeksGrade_2.[%HB],
q_sc_Corp_HB_last4weeksGrade_2.Grade AS HB_Grade,
q_sc_Corp_HB_last4weeksGrade_2.[%HB_OCavg], addreplist.GM AS RD,
addreplist.ServiceManager AS RSM, t_OCProfit_ANALYSIS_BY_CORP.NbrOfLoc INTO
q_sc_Corp_Accounts
FROM q_sc_Corp_PPAleadTime_OCAvg RIGHT JOIN
((((((((((((qryOCProfitFinalByClinicOCNetSales INNER JOIN (addreplist AS
addreplist_1 INNER JOIN q_run_sc_CorpNextRep ON addreplist_1.[Rep Name] =
q_run_sc_CorpNextRep.[Rep Name]) ON qryOCProfitFinalByClinicOCNetSales.[Acct
#] = addreplist_1.[Acct #]) LEFT JOIN q_sc_Corp_HB_last4weeksGrade_2 ON
qryOCProfitFinalByClinicOCNetSales.[Acct #] =
q_sc_Corp_HB_last4weeksGrade_2.[Acct #]) LEFT JOIN q_sc_Corp_PPAleadTime_Last
ON qryOCProfitFinalByClinicOCNetSales.[Acct #] =
q_sc_Corp_PPAleadTime_Last.Acct) LEFT JOIN q_sc_Corp_CollectRate_Last_s2 ON
qryOCProfitFinalByClinicOCNetSales.[Corp #] =
q_sc_Corp_CollectRate_Last_s2.[Corp #]) LEFT JOIN q_sc_Corp_PctPPAsOver30 ON
qryOCProfitFinalByClinicOCNetSales.[Acct #] = q_sc_Corp_PctPPAsOver30.[Acct
#]) LEFT JOIN addreplist ON qryOCProfitFinalByClinicOCNetSales.[Acct #] =
addreplist.[Acct #]) LEFT JOIN q_sc_Corp_TNF_Grade_LastPeriod ON
qryOCProfitFinalByClinicOCNetSales.[Acct #] =
q_sc_Corp_TNF_Grade_LastPeriod.[Acct #]) LEFT JOIN q_sc_Corp_ASP_LastPeriod
ON qryOCProfitFinalByClinicOCNetSales.[Acct #] =
q_sc_Corp_ASP_LastPeriod.[Acct #]) LEFT JOIN q_sc_Corp_ASP_Last12_s2 ON
qryOCProfitFinalByClinicOCNetSales.[Acct #] = q_sc_Corp_ASP_Last12_s2.[Acct
#]) LEFT JOIN q_sc_Corp_PPAleadTime_Last3 ON
qryOCProfitFinalByClinicOCNetSales.[Acct #] =
q_sc_Corp_PPAleadTime_Last3.Acct) INNER JOIN t_OCProfit_ANALYSIS_BY_CORP ON
qryOCProfitFinalByClinicOCNetSales.[Corp #] =
t_OCProfit_ANALYSIS_BY_CORP.CorpAcct) LEFT JOIN [Clinic Payment Table] ON
qryOCProfitFinalByClinicOCNetSales.[Acct #] = [Clinic Payment Table].[Account
Number]) ON q_sc_Corp_PPAleadTime_OCAvg.FYM =
qryOCProfitFinalByClinicOCNetSales.FYM
WHERE (((qryOCProfitFinalByClinicOCNetSales.[Acct #])=178838) AND
((qryOCProfitFinalByClinicOCNetSales.[Status Code])="ACTIVE"));


Maybe you can help guide me to where I should start troubleshooting the SQL
statement. I'm sure this is not the best-designed query! =)