Deleting a temp table issue

  • Thread starter Thread starter Larry Hodges
  • Start date Start date
L

Larry Hodges

Running Access 10

I have a form to launch my report. The form creates a temp table for the
report to use. When the report closes, I want to delete the temp table.

DoCmd.DeleteObject acTable, "MyTempTable"

The above works if the report is already closed. But if launching in the
Close event for the report, it doesn't. I assume that's because the table
is in use.

I've tried creating a separate hidden form to launch from the report Close
event, assuming the dataset would be closed. I've got the code in the
Activate event in the form, but it still errors out because the table is in
use.

Is there a way to close the table in the report itself, then delete the temp
table from there? If not, how do I close the table before the form I've
created tries to delete it?

Thanks in advance...
 
Larry said:
Running Access 10

I have a form to launch my report. The form creates a temp table for
the report to use. When the report closes, I want to delete the temp
table.

DoCmd.DeleteObject acTable, "MyTempTable"

The above works if the report is already closed. But if launching in
the Close event for the report, it doesn't. I assume that's because
the table is in use.

I've tried creating a separate hidden form to launch from the report
Close event, assuming the dataset would be closed. I've got the code
in the Activate event in the form, but it still errors out because
the table is in use.

Is there a way to close the table in the report itself, then delete
the temp table from there? If not, how do I close the table before
the form I've created tries to delete it?

Thanks in advance...
Click to expand...

I would just empty and repopulate a table that is NOT deleted and recreated
each time.

In a broader sense temp tables best belong in temp files. Otherwise they
lead to bloat of your main file.
 
Rick said:
I would just empty and repopulate a table that is NOT deleted and
recreated each time.

In a broader sense temp tables best belong in temp files. Otherwise
they lead to bloat of your main file.
Click to expand...

Why would that lead to bloating if you're deleting the table
programmatically each time? How is that different from populating end
deleting the data in the table?
 
Larry said:
Why would that lead to bloating if you're deleting the table
programmatically each time? How is that different from populating end
deleting the data in the table?
Click to expand...

From a bloat standpoint they're both not good, but creating and deleting
even an empty table will cause some bloat so it is the worse of the two. To
avoid bloat altogether you would use a table in a temp database, but that
doesn't address your original problem. If you tried to delete the temp
database in the close event of your report the same error would occur
(attmepting to delete an object that is still in use). However; regardless
of whether it is in the same file or a temp file emptying the contents of
the table instead of trying to delete it entirely can be done in the report
close event without a problem.
 
Rick said:
From a bloat standpoint they're both not good, but creating and
deleting even an empty table will cause some bloat so it is the worse
of the two. To avoid bloat altogether you would use a table in a
temp database, but that doesn't address your original problem. If
you tried to delete the temp database in the close event of your
report the same error would occur (attmepting to delete an object
that is still in use). However; regardless of whether it is in the
same file or a temp file emptying the contents of the table instead
of trying to delete it entirely can be done in the report close event
without a problem.
Click to expand...

Yes, it does work....thanks.
 
Back
Top