Compacting tempory file using code

[Guest]

I have a form with code that creates a tempory file for storing information
for a looping process. The tempory files bloat because information is deleted
with each loop. Is there any way to write code in this form that compacts
this tempory file with each loop or when the file gets to a certain size?

 

[John Spencer]

Do you mean you are creating temporary tables or are you creating external
files?

If you want a better answer I would suggest you share your code (or at least
relevant portions of it) and perhaps someone can suggest alternatives. One
alternative might be to create a temporary database to hold the data you need
and then delete the temporary database. Another alternative might be to hold
the temporary data in memory in an array.

Like I said, you probably need to post your code or a better explanation of what
you are attempting.

First thing is to explain what you mean by a temporary file.

 

[Guest]

Yes. It is a temporary external file. I can not post the code right at this
time as I have the code running and it takes ~ 40 hours to complete.
Essentially what I have is a series of queries in a loop that use two
tempory tables in a tempory file that is used to hold data as the code is
processed. The series of queries first uses the data (from a previous query)
in the1st table in the temporary file and then places the results of that
query into the 2nd table in the tempory file. The 2nd table is then queried
and the results placed into the 1st table and on and on and on...(approx 2000
loops). The tempory file bloats so what I want to do is to compact it at some
point in the looping process (say when it gets to so many loops or file get
to certain size). Is there anyway to do this? I'm not familiar with array's
in Access/VBA or it has been a long time - is this a better way to crack this
nut?

 

[Douglas J. Steele]

DAO has a CompactDatabase method you can use, but how large is your database
getting? If you're approaching the 2 Gb limit, then you probably should
consider a different DBMS rather than simply compacting the temporary
database. Alternatively, it might be faster to work with multiple temporary
databases. At some point, copy tables 1 and 2 to a new temporary database
and blow away the previous temporary database. That might be faster than
compacting (although you'd need to do some benchmarking)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes. It is a temporary external file. I can not post the code right at
this
time as I have the code running and it takes ~ 40 hours to complete.
Essentially what I have is a series of queries in a loop that use two
tempory tables in a tempory file that is used to hold data as the code is
processed. The series of queries first uses the data (from a previous
query)
in the1st table in the temporary file and then places the results of that
query into the 2nd table in the tempory file. The 2nd table is then
queried
and the results placed into the 1st table and on and on and on...(approx
2000
loops). The tempory file bloats so what I want to do is to compact it at
some
point in the looping process (say when it gets to so many loops or file
get
to certain size). Is there anyway to do this? I'm not familiar with
array's
in Access/VBA or it has been a long time - is this a better way to crack
this
nut?

 

[Guest]

The temporary file does go to the 2 GB limit and I get an error at that
point. What is DBMS? Database managment system?
You suggest using more than one file - what way do you suggest is the best
way to do this?

Regarding the 2GB limit if I compressing periodicly I should never get there
( the file increases by about 2000 KB per loop). What is the DAO
compactDatabase method you mentioned?

David McKnight

 

[Douglas J. Steele]

My "more than one file" suggestion was to avoid having to compact: rather
than compacting your existing database, create a new one, export the data
from the existing one, then delete the old one. As I said, though, I don't
know for sure whether this would be faster than compacting.

Look up CompactDatabase (no space) in the help file to find out details
about using the DAO CompactDatabase method (you need to be in the VB Editor
when you do this).

In a nutshell, you use:

DBEngine.CompactDatabase olddb, newdb

Another alternative to the DAO CompactDatabase method is to use JRO (Jet
Replication Objects). There's a KB article that explains how to use it at
http://support.microsoft.com/?id=230501


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The temporary file does go to the 2 GB limit and I get an error at that
point. What is DBMS? Database managment system?
You suggest using more than one file - what way do you suggest is the best
way to do this?

Regarding the 2GB limit if I compressing periodicly I should never get
there
( the file increases by about 2000 KB per loop). What is the DAO
compactDatabase method you mentioned?

David McKnight