Microsoft Access running out of temp space (Error 3183)

Question

I inherited this Frankenstein application at work that utilizes an Access 2007 DB frontend with linked tables to an Oracle 10g DB.

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.

I verified that Access is creating a 2GB temp file which is the maximum amount allowed in the system temp directory.

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.

I had the Oracle DBA double the temp space, but still didn't fix the issue.

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.

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.

This is the Access RAW SQL.. Understand I didn't write this.. The Oracle tables at least didn't have space and ampersands. :)

    SELECT DISTINCT [Event: Overhaul & Repair].BASE_PART_NUMBER,
  [Event: Overhaul & Repair].PERIOD,
  [Event: Overhaul & Repair].RECEIVED_BY_FACILITY_DATE,
  [Event: Overhaul & Repair].PART_NUMBER,
  [Event: Overhaul & Repair].CONFIGURATION_RECEIVED,
  [Event: Overhaul & 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 & Repair].REMOVAL_JUSTIFIED_FLAG_ON_OR,
  [Event: Overhaul & Repair].CUSTOMER_NAME_SUBMITTING,
  [Findings: Feature Findings Detail].AIRCRAFT_TYPE,
  [Event: Overhaul & Repair].AIRCRAFT_REG_NUMBER,
  [Event: Overhaul & Repair Text].NOTE_TEXT,
  [Event: Overhaul & Repair].TIME_SINCE_NEW_ON_OR,
  [Event: Overhaul & Repair].TIME_SINCE_INSTALL,
  [Event: Overhaul & Repair].TIME_SINCE_OVERHAUL_ON_OR,
  [Event: Overhaul & Repair].FACILITY_NAME,
  [Event: Overhaul & Repair].EVENT_SEQNO
FROM ([Event: Overhaul & REPAIR]
LEFT JOIN [Event: Overhaul & REPAIR Text]
ON [Event: Overhaul & REPAIR].EVENT_SEQNO=[Event: Overhaul & REPAIR Text].EVENT_SEQNO)
LEFT JOIN [Findings: Feature Findings Detail]
ON [Event: Overhaul & REPAIR].EVENT_SEQNO=[Findings: Feature Findings Detail].EVENT_SEQUENCE_NUMBER
WHERE ((([Event: Overhaul & Repair].BASE_PART_NUMBER)=[PART NUMBER])
AND (([Event: Overhaul & 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 & Repair Text].NOTE_TEXT)="R"));

Answer 1

This is the error description for AccessError(3183):

The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result.

When the Access db engine needs space for its work set, it uses a temporary disk file. That file is size-restricted to a max of 2 GB, the same as a regular Access db file.

So apparently the Access db engine must pull down so much data from Oracle that its work set space requirement exceeds 2 GB. My hunch is that happens even when you adjust the query constraints so that no rows match because Access must first pull so much data for the intermediate set before it can even determine none of the candidate rows satisfy the constraint.

You need to find some way to limit the amount of data Access must process. Since the Oracle view you attempted created a different problem, I don't know what else to suggest. If the final result set from the Oracle query is reasonably-sized, and if you can use the result set as read-only from the Access side, use an Access pass-through query. If that's not satisfactory, show us the Access SQL from a problem query and see whether we can fix it.

Regarding "Any ideas why this would start happening ...", most likely the volume of the Oracle data has grown over time.