File size grows when processing through loop

[Guest]

I have a form with the command button that loops through a set of queries.
The problem I have is that the database file size grows +20 times as it goes
through the loop (at the 665 loop through) it ultimatly get too large and I
get run time error 3001. The file size can be compacted down from this large
size of 2000 MB to 85 MB after the error occurs. Is there something I can do
to prevent his from happening?

 

[Dale Fye]

David,

Post the code behind your command button, the SQL of the queries you are
running, and more information about your table structure(table names,
fields, datatypes).

Usually, when you are doing what you are doing (looping and running queries
inside the loop), there is a more efficient way to write the querys to
accomplish your goals.

Dale

 

[Guest]

Below is the code, but an explaination first what it does.
The basic goal is to rank teams based on 1) avg margin of victory + the avg
margin of victory of their opponets + their opp + their opp, take that
weighted value (Power Ranking) and 2) Compare the Power ranking verse actual
score (adjusted for home field advantage) so that if a team with a power
ranking of 20 plays one with a power ranking of 10 and wins by 8 the higher
rank team would have a game performance of 19 (((20 +30)/2) +8/2)) and the
lower ranked team performance would be 11. Performance of each team is
averaged together for each game to come up with a "quality ranking" this
ranking is used for a second comparision and then that to a third and so on
......

Because of the complexity I have found that if I break down the task into
several series of queries and dump these query results into a table my system
can handle for the most part...accept I need to take the number of loops
maybe one or two thousand iterations before it stablizes and that is when I
have problems.

The way I have queries named gives hints of the function and I have not
included those queries here - I will if you still think it will help. Queries
that begin with numbers 111, 116, 121 & 121e find #1 process described above,
143 and 153 queries and tables are to handle the looping process.


Private Sub cmdDiv1ARanking_Click()
txtTimeStart.Value = Now

counter = 0


If (IsNull(Me.txtSeason) Or IsNull(Me.txtEndSeason)) Then
MsgBox "Both years required."
Else


EndSeason = txtEndSeason.Value
Season = txtSeason.Value - 1



Do
Season = Season + 1
txtSeason.Value = Season

Refresh

DoCmd.OpenQuery "Season Division Conference School - Duplicates",
acViewNormal, acReadOnly
DoCmd.Close
DoCmd.OpenTable "Filter Parameters", acNormal, acEdit
DoCmd.Close

strSql = "DELETE * FROM [111 Avg Adjusted Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [111 Avg Adjusted Margin DB] SELECT [111 Avg
Adjusted Margin].* FROM [111 Avg Adjusted Margin]"
CurrentDb.Execute strSql, dbFailOnError

txt111.Value = "Completed"


strSql = "DELETE * FROM [116 Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [116 Opp Avg Margin DB] SELECT [116 Opp Avg
Margin].* FROM [116 Opp Avg Margin]"
CurrentDb.Execute strSql, dbFailOnError

txt116.Value = "Completed"

strSql = "DELETE * FROM [121 Opp Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [121 Opp Opp Avg Margin DB] SELECT [121 Opp Opp
Avg Margin].* FROM [121 Opp Opp Avg Margin]"
CurrentDb.Execute strSql, dbFailOnError

txt121.Value = "Completed"
strSql = "DELETE * FROM [121e Opp Opp Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [121e Opp Opp Opp Avg Margin DB] SELECT [121e Opp
Opp Opp Avg Margin].* FROM [121e Opp Opp Opp Avg Margin]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [122 Power Rankings DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [122 Power Rankings DB] SELECT [122 Power
Rankings].* FROM [122 Power Rankings]"
CurrentDb.Execute strSql, dbFailOnError


strSql = "DELETE * FROM [143 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [143 Quality Scores Union Query DB] SELECT [143c
Quality Scores].* FROM [143c Quality Scores]"
CurrentDb.Execute strSql, dbFailOnError

counter = 0
myNum = 1
Do
myNum = myNum + 1
Text_LoopNo.Value = myNum

strSql = "DELETE * FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [153 Quality Scores Union Query DB] SELECT [153c
Quality Scores].* FROM [153c Quality Scores]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [143 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [143 Quality Scores Union Query DB] SELECT [153
Quality Scores Union Query DB].* FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError


counter = counter + 1
Loop Until myNum = 1024
txtTimeFinished.Value = Now


strSql = "DELETE * FROM [802 Div 1A Rankings]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [802 Div 1A Rankings] SELECT [153 Quality Scores
Union Query DB].* FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError


counter = counter + 1
Loop Until Season = EndSeason
End If
txtTimeFinished.Value = Now
End Sub

 

[John Nurick]

Hi David,

It looks as if you're doing an awful lot of appending and deleting
records in table that hold values for intermediate steps in your
calculation. It's certain that all the appending and deleting is what is
bloating your database: Jet does not recover space used by deleted
records until you explicitly compact the database.

So a sensible step would be to put these - effectively temporary -
tables into a separate mdb file. This is a common and strongly
recommended technique to overcome the bloating problem when database
operations require creation and deletion of temporary records.

You can either use a "standing" mdb file for these intermediate tables,
and run code to compact it after each round of deletions - or have your
code create a "throwaway" mdb as often as needed, deleting it after use.

This should overcome your current bloating problem. Later, there may
still be benefits in re-examining your algorithm along the lines Dale
suggested.

Below is the code, but an explaination first what it does.
The basic goal is to rank teams based on 1) avg margin of victory + the avg
margin of victory of their opponets + their opp + their opp, take that
weighted value (Power Ranking) and 2) Compare the Power ranking verse actual
score (adjusted for home field advantage) so that if a team with a power
ranking of 20 plays one with a power ranking of 10 and wins by 8 the higher
rank team would have a game performance of 19 (((20 +30)/2) +8/2)) and the
lower ranked team performance would be 11. Performance of each team is
averaged together for each game to come up with a "quality ranking" this
ranking is used for a second comparision and then that to a third and so on
.....

Because of the complexity I have found that if I break down the task into
several series of queries and dump these query results into a table my system
can handle for the most part...accept I need to take the number of loops
maybe one or two thousand iterations before it stablizes and that is when I
have problems.

The way I have queries named gives hints of the function and I have not
included those queries here - I will if you still think it will help. Queries
that begin with numbers 111, 116, 121 & 121e find #1 process described above,
143 and 153 queries and tables are to handle the looping process.


Private Sub cmdDiv1ARanking_Click()
txtTimeStart.Value = Now

counter = 0


If (IsNull(Me.txtSeason) Or IsNull(Me.txtEndSeason)) Then
MsgBox "Both years required."
Else


EndSeason = txtEndSeason.Value
Season = txtSeason.Value - 1



Do
Season = Season + 1
txtSeason.Value = Season

Refresh

DoCmd.OpenQuery "Season Division Conference School - Duplicates",
acViewNormal, acReadOnly
DoCmd.Close
DoCmd.OpenTable "Filter Parameters", acNormal, acEdit
DoCmd.Close

strSql = "DELETE * FROM [111 Avg Adjusted Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [111 Avg Adjusted Margin DB] SELECT [111 Avg
Adjusted Margin].* FROM [111 Avg Adjusted Margin]"
CurrentDb.Execute strSql, dbFailOnError

txt111.Value = "Completed"


strSql = "DELETE * FROM [116 Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [116 Opp Avg Margin DB] SELECT [116 Opp Avg
Margin].* FROM [116 Opp Avg Margin]"
CurrentDb.Execute strSql, dbFailOnError

txt116.Value = "Completed"

strSql = "DELETE * FROM [121 Opp Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [121 Opp Opp Avg Margin DB] SELECT [121 Opp Opp
Avg Margin].* FROM [121 Opp Opp Avg Margin]"
CurrentDb.Execute strSql, dbFailOnError

txt121.Value = "Completed"
strSql = "DELETE * FROM [121e Opp Opp Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [121e Opp Opp Opp Avg Margin DB] SELECT [121e Opp
Opp Opp Avg Margin].* FROM [121e Opp Opp Opp Avg Margin]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [122 Power Rankings DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [122 Power Rankings DB] SELECT [122 Power
Rankings].* FROM [122 Power Rankings]"
CurrentDb.Execute strSql, dbFailOnError


strSql = "DELETE * FROM [143 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [143 Quality Scores Union Query DB] SELECT [143c
Quality Scores].* FROM [143c Quality Scores]"
CurrentDb.Execute strSql, dbFailOnError

counter = 0
myNum = 1
Do
myNum = myNum + 1
Text_LoopNo.Value = myNum

strSql = "DELETE * FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [153 Quality Scores Union Query DB] SELECT [153c
Quality Scores].* FROM [153c Quality Scores]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [143 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [143 Quality Scores Union Query DB] SELECT [153
Quality Scores Union Query DB].* FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError


counter = counter + 1
Loop Until myNum = 1024
txtTimeFinished.Value = Now


strSql = "DELETE * FROM [802 Div 1A Rankings]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [802 Div 1A Rankings] SELECT [153 Quality Scores
Union Query DB].* FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError


counter = counter + 1
Loop Until Season = EndSeason
End If
txtTimeFinished.Value = Now
End Sub

 

[Guest]

Any clues on how to write code to compact file (doing this manually takes you
out of the file) and or creating and deleting tempory table?
--
David McKnight

Hi David,

It looks as if you're doing an awful lot of appending and deleting
records in table that hold values for intermediate steps in your
calculation. It's certain that all the appending and deleting is what is
bloating your database: Jet does not recover space used by deleted
records until you explicitly compact the database.

So a sensible step would be to put these - effectively temporary -
tables into a separate mdb file. This is a common and strongly
recommended technique to overcome the bloating problem when database
operations require creation and deletion of temporary records.

You can either use a "standing" mdb file for these intermediate tables,
and run code to compact it after each round of deletions - or have your
code create a "throwaway" mdb as often as needed, deleting it after use.

This should overcome your current bloating problem. Later, there may
still be benefits in re-examining your algorithm along the lines Dale
suggested.

Below is the code, but an explaination first what it does.
The basic goal is to rank teams based on 1) avg margin of victory + the avg
margin of victory of their opponets + their opp + their opp, take that
weighted value (Power Ranking) and 2) Compare the Power ranking verse actual
score (adjusted for home field advantage) so that if a team with a power
ranking of 20 plays one with a power ranking of 10 and wins by 8 the higher
rank team would have a game performance of 19 (((20 +30)/2) +8/2)) and the
lower ranked team performance would be 11. Performance of each team is
averaged together for each game to come up with a "quality ranking" this
ranking is used for a second comparision and then that to a third and so on
.....

Because of the complexity I have found that if I break down the task into
several series of queries and dump these query results into a table my system
can handle for the most part...accept I need to take the number of loops
maybe one or two thousand iterations before it stablizes and that is when I
have problems.

The way I have queries named gives hints of the function and I have not
included those queries here - I will if you still think it will help. Queries
that begin with numbers 111, 116, 121 & 121e find #1 process described above,
143 and 153 queries and tables are to handle the looping process.


Private Sub cmdDiv1ARanking_Click()
txtTimeStart.Value = Now

counter = 0


If (IsNull(Me.txtSeason) Or IsNull(Me.txtEndSeason)) Then
MsgBox "Both years required."
Else


EndSeason = txtEndSeason.Value
Season = txtSeason.Value - 1



Do
Season = Season + 1
txtSeason.Value = Season

Refresh

DoCmd.OpenQuery "Season Division Conference School - Duplicates",
acViewNormal, acReadOnly
DoCmd.Close
DoCmd.OpenTable "Filter Parameters", acNormal, acEdit
DoCmd.Close

strSql = "DELETE * FROM [111 Avg Adjusted Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [111 Avg Adjusted Margin DB] SELECT [111 Avg
Adjusted Margin].* FROM [111 Avg Adjusted Margin]"
CurrentDb.Execute strSql, dbFailOnError

txt111.Value = "Completed"


strSql = "DELETE * FROM [116 Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [116 Opp Avg Margin DB] SELECT [116 Opp Avg
Margin].* FROM [116 Opp Avg Margin]"
CurrentDb.Execute strSql, dbFailOnError

txt116.Value = "Completed"

strSql = "DELETE * FROM [121 Opp Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [121 Opp Opp Avg Margin DB] SELECT [121 Opp Opp
Avg Margin].* FROM [121 Opp Opp Avg Margin]"
CurrentDb.Execute strSql, dbFailOnError

txt121.Value = "Completed"
strSql = "DELETE * FROM [121e Opp Opp Opp Avg Margin DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [121e Opp Opp Opp Avg Margin DB] SELECT [121e Opp
Opp Opp Avg Margin].* FROM [121e Opp Opp Opp Avg Margin]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [122 Power Rankings DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [122 Power Rankings DB] SELECT [122 Power
Rankings].* FROM [122 Power Rankings]"
CurrentDb.Execute strSql, dbFailOnError


strSql = "DELETE * FROM [143 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [143 Quality Scores Union Query DB] SELECT [143c
Quality Scores].* FROM [143c Quality Scores]"
CurrentDb.Execute strSql, dbFailOnError

counter = 0
myNum = 1
Do
myNum = myNum + 1
Text_LoopNo.Value = myNum

strSql = "DELETE * FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [153 Quality Scores Union Query DB] SELECT [153c
Quality Scores].* FROM [153c Quality Scores]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [143 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [143 Quality Scores Union Query DB] SELECT [153
Quality Scores Union Query DB].* FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError


counter = counter + 1
Loop Until myNum = 1024
txtTimeFinished.Value = Now


strSql = "DELETE * FROM [802 Div 1A Rankings]"
CurrentDb.Execute strSql, dbFailOnError
strSql = "INSERT INTO [802 Div 1A Rankings] SELECT [153 Quality Scores
Union Query DB].* FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError


counter = counter + 1
Loop Until Season = EndSeason
End If
txtTimeFinished.Value = Now
End Sub

 

[John Nurick]

There's sample code for creating and using temporary MDB files at
http://www.tek-tips.com/faqs.cfm?fid=5980 .

It's not possible to compact an mdb file from code running in that file.
But if you use a separate mdb for your temporary results you can compact
that from code running in your main mdb: just make sure that all linked
tables or other connections to the "second" mdb are closed.

 

[Guest]

This was very helpful, but I'm have a little problem adjusting this code to
my situation.

Since I am using one table to run the query from and another one to hold the
results of the query it seems I need to have two tempory tables. The sample
code that is in link only uses one tempory file and table. I don't see how I
can query a table and then paste the results back into the same table that
the query used -

I used the following code to loop through deleting, querying, pasteing and
then I modified this code to use tempory files as suggested. The problem I
had is the file I wanted to paste results to was not the current file or did
not exist. So using two tempory tables is not working. Suggestions?

counter = 0
myNum = 1
Do
myNum = myNum + 1
Text_LoopNo.Value = myNum

strSql = "DELETE * FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [153 Quality Scores Union Query DB] SELECT [153c
Quality Scores].* FROM [153c Quality Scores]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [143 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [143 Quality Scores Union Query DB] SELECT [153
Quality Scores Union Query DB].* FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError


counter = counter + 1
Loop Until myNum = 1280

 

[John Nurick]

I'm not sure what you mean by "paste the results".

Unless I'm mistaken, the function at
http://www.tek-tips.com/faqs.cfm?fid=5980 creates one temporary MDB file
containing as many tables as you care to define, and connects each of
them as a linked table in your "permanent" MDB.

For each table, you need to create a set of field definitions in
ztblTempStructure, the data definition table that the function requires.

You can access data in a table in another MDB file without needing a
linked table, by using syntax like this
SELECT * FROM [TableName] IN "D:\Folder\Filename.mdb";
or
... INTO [TableName] IN "D:\Folder\Filename.mdb" ...

This was very helpful, but I'm have a little problem adjusting this code to
my situation.

Since I am using one table to run the query from and another one to hold the
results of the query it seems I need to have two tempory tables. The sample
code that is in link only uses one tempory file and table. I don't see how I
can query a table and then paste the results back into the same table that
the query used -

I used the following code to loop through deleting, querying, pasteing and
then I modified this code to use tempory files as suggested. The problem I
had is the file I wanted to paste results to was not the current file or did
not exist. So using two tempory tables is not working. Suggestions?

counter = 0
myNum = 1
Do
myNum = myNum + 1
Text_LoopNo.Value = myNum

strSql = "DELETE * FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [153 Quality Scores Union Query DB] SELECT [153c
Quality Scores].* FROM [153c Quality Scores]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [143 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [143 Quality Scores Union Query DB] SELECT [153
Quality Scores Union Query DB].* FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError


counter = counter + 1
Loop Until myNum = 1280

 

[Guest]

Thanks, I was able to modify the linked code so it would go back and forth to
two tables in one temporary file.

This method of using tempory files does seems to take much longer to cycle
through loops versus using "direct" files, is this to be expected?

Also I would like to see progress on my form on how the query is progressing
through the loops (as the computer can take a day or so to finish). I have a
counter that should update a text box - but the problem appears to be that
query takes so much computer resources that the until the query is completed
through all loops no refreshing goes on with the screen - is there a way to
pause the query for a second to catch a breath (refresh) and then go on after
each loop?
--
David McKnight

I'm not sure what you mean by "paste the results".

Unless I'm mistaken, the function at
http://www.tek-tips.com/faqs.cfm?fid=5980 creates one temporary MDB file
containing as many tables as you care to define, and connects each of
them as a linked table in your "permanent" MDB.

For each table, you need to create a set of field definitions in
ztblTempStructure, the data definition table that the function requires.

You can access data in a table in another MDB file without needing a
linked table, by using syntax like this
SELECT * FROM [TableName] IN "D:\Folder\Filename.mdb";
or
... INTO [TableName] IN "D:\Folder\Filename.mdb" ...

This was very helpful, but I'm have a little problem adjusting this code to
my situation.

Since I am using one table to run the query from and another one to hold the
results of the query it seems I need to have two tempory tables. The sample
code that is in link only uses one tempory file and table. I don't see how I
can query a table and then paste the results back into the same table that
the query used -

I used the following code to loop through deleting, querying, pasteing and
then I modified this code to use tempory files as suggested. The problem I
had is the file I wanted to paste results to was not the current file or did
not exist. So using two tempory tables is not working. Suggestions?

counter = 0
myNum = 1
Do
myNum = myNum + 1
Text_LoopNo.Value = myNum

strSql = "DELETE * FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [153 Quality Scores Union Query DB] SELECT [153c
Quality Scores].* FROM [153c Quality Scores]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [143 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError

strSql = "INSERT INTO [143 Quality Scores Union Query DB] SELECT [153
Quality Scores Union Query DB].* FROM [153 Quality Scores Union Query DB]"
CurrentDb.Execute strSql, dbFailOnError


counter = counter + 1
Loop Until myNum = 1280

 

[John Nurick]

Thanks, I was able to modify the linked code so it would go back and forth to
two tables in one temporary file.

This method of using tempory files does seems to take much longer to cycle
through loops versus using "direct" files, is this to be expected?

I've never investigated. I'd expect it to take a little longer in
general - but it would depend very much on just what you're doing in
your loops and how many times you go round them.

Also I would like to see progress on my form on how the query is progressing
through the loops (as the computer can take a day or so to finish). I have a
counter that should update a text box - but the problem appears to be that
query takes so much computer resources that the until the query is completed
through all loops no refreshing goes on with the screen - is there a way to
pause the query for a second to catch a breath (refresh) and then go on after
each loop?

As far as I know there's no way to get a progress report from DAO on the
execution of a query (but I've never researched this). If you call
DoEvents at strategic points in your VBA code - e.g. after each lengthy
CurrentDB.Execute - it allows other things to catch up.

I don't know what's in your data or what you're doing to it, but unless
you're a SQL wizard you're probably making more use of temporary tables
and append and delete queries than you need to. The more you can do with
set-based SQL operations rather than procedural VBA code, and the less
you write to disk the faster (in general) your code will run.

For general performance tips, see
http://www.granite.ab.ca/access/performancefaq.htm

 

[Guest]

Oops, looks like I forgot to ask an important question. Now that I have my
tempory file how do I compact it with code when it gets too big in the
looping process without losing access to the tables so I can continue using
the data in them for the next loop?