title: Microsoft Access running out of temp space (Error 3183)
body: <p>I inherited this Frankenstein application at work that utilizes an Access 2007 DB frontend with linked tables to an Oracle 10g DB.</p>
  
  <p>Users have started reporting that a lot of the queries are failing with an error 3183 out of temporary space. These same queries have run fine a few weeks back.</p>
  
  <p>I verified that Access is creating a 2GB temp file which is the maximum amount allowed in the system temp directory.</p>
  
  <p>Even if I run the query using parameters that would not return data, I still get the error. Doing a SQL trace shows that some of the queries are trying to run a queryset that would return about 14 million rows. I know some of the queries are poorly designed, but that is not an easy thing to change.</p>
  
  <p>I had the Oracle DBA double the temp space, but still didn't fix the issue. </p>
  
  <p>I guess my question is does an Access DB use the temp space available on the linked ODBC database first then use the temp file if no Oracle temp tablespace is available? Any ideas why this would start happening or is it just that the amount of the data in the Oracle database has exceeded the ability of Access? The way the application was created had been working up until a few weeks back or so. </p>
  
  <p>Understand redesigning this app is not an easy option. I am more looking for an idea as to why this would have worked before and started happening recently.</p>
  
  <p>This is the Access RAW SQL.. Understand I didn't write this.. The Oracle tables at least didn't have space and ampersands. :)</p>
  
  <pre><code>    SELECT DISTINCT [Event: Overhaul &amp; Repair].BASE_PART_NUMBER,
    [Event: Overhaul &amp; Repair].PERIOD,
    [Event: Overhaul &amp; Repair].RECEIVED_BY_FACILITY_DATE,
    [Event: Overhaul &amp; Repair].PART_NUMBER,
    [Event: Overhaul &amp; Repair].CONFIGURATION_RECEIVED,
    [Event: Overhaul &amp; Repair].PART_SERIAL_NBR,
    [Findings: Feature Findings Detail].SUBASSEMBLY_NAME,
    [Findings: Feature Findings Detail].COMPONENT_NAME,
    [Findings: Feature Findings Detail].FEATURE_NAME,
    [Findings: Feature Findings Detail].FAILURE_DESCRIPTION,
    [Findings: Feature Findings Detail].PRIMARY_FAILURE_IND,
    [Event: Overhaul &amp; Repair].REMOVAL_JUSTIFIED_FLAG_ON_OR,
    [Event: Overhaul &amp; Repair].CUSTOMER_NAME_SUBMITTING,
    [Findings: Feature Findings Detail].AIRCRAFT_TYPE,
    [Event: Overhaul &amp; Repair].AIRCRAFT_REG_NUMBER,
    [Event: Overhaul &amp; Repair Text].NOTE_TEXT,
    [Event: Overhaul &amp; Repair].TIME_SINCE_NEW_ON_OR,
    [Event: Overhaul &amp; Repair].TIME_SINCE_INSTALL,
    [Event: Overhaul &amp; Repair].TIME_SINCE_OVERHAUL_ON_OR,
    [Event: Overhaul &amp; Repair].FACILITY_NAME,
    [Event: Overhaul &amp; Repair].EVENT_SEQNO
  FROM ([Event: Overhaul &amp; REPAIR]
  LEFT JOIN [Event: Overhaul &amp; REPAIR Text]
  ON [Event: Overhaul &amp; REPAIR].EVENT_SEQNO=[Event: Overhaul &amp; REPAIR Text].EVENT_SEQNO)
  LEFT JOIN [Findings: Feature Findings Detail]
  ON [Event: Overhaul &amp; REPAIR].EVENT_SEQNO=[Findings: Feature Findings Detail].EVENT_SEQUENCE_NUMBER
  WHERE ((([Event: Overhaul &amp; Repair].BASE_PART_NUMBER)=[PART NUMBER])
  AND (([Event: Overhaul &amp; Repair].PERIOD) BETWEEN [START DATE YYYYMM] AND [END DATE YYYYMM])
  AND (([Findings: Feature Findings Detail].PRIMARY_FAILURE_IND)="PF"
  OR ([Findings: Feature Findings Detail].PRIMARY_FAILURE_IND) IS NULL)
  AND (([Event: Overhaul &amp; Repair Text].NOTE_TEXT)="R"));
  </code></pre>