This error occurs when the MS Access query runs out of temporary memory. An example
of the error message is shown below:
Microsoft OLE DB Provider for ODBC Drivers error '8007000e'
[Microsoft][ODBC Microsoft Access Driver] Not enough space on temporary disk.
/yourscript.asp, line 33
This error message indicates that the scripts has run into problems due to a
lack of temporary disk space. This does not indicate that the server has
run out of space but that Access cannot cope with any more temporary files or a
temporary file that is any larger.
The most common and simplest to cure cause is that writable record sets are
being opened where they are not necessary. You should try to minimize the number of JET*.tmp files
you need to create. For example, when opening the database solely to retrieve data
(no UPDATE, INSERT or DELETE), do so in adModeRead mode. This way, Access will use
the least amount of temporary files, since it doesn't need to prepare room or logic
for changing existing data. Here is an example of opening an Access database in
adModeRead mode:
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.mode = 1 ' adModeRead
conn.open "<connection string>"
' ...
%>
If you have tried this then you need to look at the size of temporary files
being created. In this case this error usually indicates that a query to an Access MDB file is returning
a recordset that is too large or it requires too much temporary space to search
or sort a result set. The end result in either case is that the Access query is
using up all available memory on the server and failing with the error.
In DBA terms, what this means is that you probably have a query that is doing
one or more "full-table scans" and there is not enough RAM to hold all the data
being returned, searched or sorted.
The "temporary disk" actually means physical memory, we have seen this before,
and it has nothing to do with the physical disk space, (of which there is plenty).
To overcome this error, you can try the following:
Usually, steps 1. and 2. are done together. First check your Access MDB file
and make sure that all tables have primary keys and make sure that there are indexes
on all columns used in join operations or are used in WHERE, ORDER BY and GROUP
BY clauses.
You may also want to add multi-column indexes if you have queries that search
or sort on multiple fields. This is especially helpful if your query returns rows
in a particular multi-column order.
Once you are satisfied that your database structure is tuned you should see if
you can rewrite your queries so they run as efficiently as possible. The following
paragraphs have some tips and hints on writing efficient queries.
Avoiding Full-Table Scans
Try to eliminate functions from joins and WHERE clauses and sorts. Access cannot
use indexes on a function. For example, if you have a WHERE clause that looks like:
WHERE Mid(fldText,1,4)='TEST'
it will force Access to search the entire table and compare the first 4 characters
of the given column in every row of the table. This is a full-table scan. In this
example, adding a code column, putting an index on it and recoding the query to
read:
WHERE fldCode='TEST'
is far more efficient then using the MID function since Access can now use the
index for searching instead of performing a full-table scan.
Only Query the Fields You Need
Another change that may help is to eliminate unneeded fields from your query.
If you code a query such as:
SELECT * FROM Customers
and you are only looking for the name and email address, you should rewrite the
query as:
SELECT Name,Email FROM Customers
This can significantly reduce your queries memory requirements since Access only
needs to reserve space for the few fields being returned and not the entire row.
Avoid Memo and Large Text Fields
If you are retrieving memo fields or long text fields, you might eliminate the
field from the query and run a separate query to retrieve the large field during
your recordset processing loop. For example:
rst1 = conn.execute("SELECT ID FROM Products")
while rst1.eof = false
rst2 = conn.execute("SELECT Desc FROM Products WHERE ID=" & rst1("ID")
response.write rst2("Desc")
wend
By coding your query this way, Access only needs enough memory for all the keys
and a single copy of the large text field. The overall processing time may be a
bit slower, but if the alternative is a runtime error, this is usually an acceptable
compromise.
In some cases, this may actually be faster, as Access will usually cache all
retrieved ID values from the first query and then use them to quickly find the row
containing the large data field during the second query.
Other Things You Can Do
In addition the above tips, you should look at your application with a critical
eye and see if there are other means you can achieve better efficiencies. You may
be able to break large tables into smaller ones or redesign the data schema or re-code
your scripts in other ways to help performance.
When to Upgrade
If you have tuned your schema, re-coded your queries and you still run into the
error, usually the only solution is to move to SQL Server or Oracle.
Access works quite well for small to medium volumes of data, however is is not
a true database server. Due to this, it can place unreasonable demands on the host
computer which may exceed available resources.
If your site is on a shared webserver keep in mind that the memory available
to your query is usually less than what you can get on well equipped modern workstation.
A query that need 10 or even 20 MB is usually not a problem on our servers, but
you might be suprised to find your site is running queries that need 100MB or more.
On your local workstation this is usually not a problem, but it is a real problem
when a huge query is competing for resources on a shared server.
This is especially true for busy sites. If you multiply the 100MB query by just
10 users, your query might end up asking the server for 1GB or more of memory. Obviously,
this will not work well in a shared server environment.
If you database is already fairly large and is growing, at some point you will
probably have no choice but to move to a full-featured database server.