Link to home
Start Free TrialLog in
Avatar of wmckinney
wmckinneyFlag for United States of America

asked on

Not enough space on temporary disk - New problem - Old program

I have a Microsoft Access program that I run each quarter. It ran in June just fine and now it gets the above referenced message. I have confirmed that the Jet*.tmp file has reached the 2,092,996 size and that the actual database files are all less than 1 GB. I'm concerned that some "update" has come across that has changed the operating characteristics of Access but, aside from the cause, is there some technique that I can include in the program to cause the Jet*.tmp to reset? I am compacting the secondary MDB's during the process but have no idea how to get the tmp files to reset. Based on the number of EE questions that reference this problem I am guessing that this has a myriad of solutions but I have tried all that make sense. I have already increased all the PAGEFILE's.
Avatar of aianrnoens
aianrnoens

Avatar of wmckinney

ASKER

Alan - I read through the article and I see where he seems to have achieved a solution to his problem... However, I strongly suspect that the problem I have is related to the Jet*.tmp reaching 2 GB... That is the maximum size for an MDB and it seems likely that the same threshold is what is causing the problem I am experiencing... What is incredibly interesting is that I run this program for each quarter from 1996 to the current quarter every three months and I successfully ran it in July for the period ending June 30th... Now it gets the error in the 2nd quarter of 2003... It seems to me that the same program with no changes should at least run as far for the current quarter as it did in June but now it fails several cycles earlier than it accomplished successfully 3 months ago... I have plenty of free disk space on all drives and the system RAM is at 4 GB just as it was in the previous run...

Wayne
Wayne,
Try compacting your mdb files using Jet Compact instead of the Access compact utility.  The Jet compact does not open and close tables like the Access Compact, and therefore, has a much smaller footprint for the use of temp disk space.  The function code below was graciously provided as a tip on the tip page of  www.aadconsulting.com .  Hope this helps.

Function CompactDb(strSourceDB As String, strDestDB As String)

Dim jetEngine As JRO.JetEngine
Dim strSourceConnect As String
Dim strDestConnect As String


' Build connection strings for SourceConnection
' and DestConnection arguments
strSourceConnect = "Data Source=" & strSourceDB
strDestConnect = "Data Source=" & strDestDB

Set jetEngine = New JRO.JetEngine

jetEngine.CompactDatabase strSourceConnect, strDestConnect

Set jetEngine = Nothing

End Function
 
I'm not having any problems compacting the databases. I have one primary database and two secondary databases where all the data resides... One of the secondary databases contains only summary result data; it is cleared at the beginning of the run and never gets larger than 1 GB... The other secondary database contains working files and at the end of each loop through the data they are cleared and the database is compacted... At the time I get the error the primary database is 1,112 KB, the results database is 140 KB and the work area is 208 KB... However, the file JET530.tmp is at 2,092,996 KB... If I delete the Jet*.tmp before the run, Access will recreate a Jet*.tmp file and it will once again get the error with similar results...
ASKER CERTIFIED SOLUTION
Avatar of puppydogbuddy
puppydogbuddy

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial