Deleting a tempory table upon closing a report

[Stephen @ ZennHAUS]

Hi Guys and Gals,

I have a number of reports that are based on a temporary table that is
generated from code based queries. As I don't want the table to remain in
the database once the report is completed, I am trying to delete the table
when the report closes using the following code.

Private Sub Report_Close()

DoCmd.DeleteObject acTable, "Temp"

End Sub

However, when I do this I get the error that the table is still in use by
the report and therefore cannot be deleted.

Any suggestions on how I could delete this table would be appreciated.

Cheers

Stephen @ ZennHAUS

 

[Duane Hookom]

I don't think you can do this in the report code.

You could add code to the timer event of the form that opens the report that
deletes the temporary table. Make sure the timer interval is set to 0 so it
doesn't run. Then in the On Close event of the report, set the form's timer
interval to something like 2000 so the table gets deleted about 2 seconds
after the report closes. Make sure the code to delete the table sets the
timer back to 0.

Not sure if this will work in all instances since previewing and then
printing the report from the preview screen might delete the table
prematurely.

 

[Marshall Barton]

Hi Guys and Gals,

I have a number of reports that are based on a temporary table that is
generated from code based queries. As I don't want the table to remain in
the database once the report is completed, I am trying to delete the table
when the report closes using the following code.

Private Sub Report_Close()

DoCmd.DeleteObject acTable, "Temp"

End Sub

However, when I do this I get the error that the table is still in use by
the report and therefore cannot be deleted.

Any suggestions on how I could delete this table would be appreciated.

If the temp table is in the backend db, it should not be
there.

If it's in your front end database, you should delete the
records, not the table. Either way, it not a good design
for several reasons.

If your app is not split into frontend and backend dbs, it
should be. For more information, see:
http://www.granite.ab.ca/access/splitapp/index.htm

Take a hard look at:
http://www.granite.ab.ca/access/temptables.htm
for a better way to deal with temp tables.

As to your specific question, you need to delete the table
(or the records it contains), just before creating (or
populating the temp table. Deleting it anywhere else leaves
you open to problems when there is a crash, power outage or
unusual user action (e.g. close, shutdown, etc) that
terminates Access before the table is deleted.

 

[Armen Stein]

As to your specific question, you need to delete the table
(or the records it contains), just before creating (or
populating the temp table. Deleting it anywhere else leaves
you open to problems when there is a crash, power outage or
unusual user action (e.g. close, shutdown, etc) that
terminates Access before the table is deleted.

Hi Marsh,

I agree with this approach too - since you have to ensure that the
table is empty before you begin, just run the delete code then, and
leave it in there for diagnostic purposes after the report runs.

The only exception might be when you don't want to leave the data in
the temp table for security purposes.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Amicron]

Do you want to delete the actual TABLE or just the data? If you only
want to erase the data, just run a DELETE QUERY when the report
closes.

Richard Rost
http://www.AccessLearningZone.com?key=usenet

 

[Stephen @ ZennHAUS]

The database is split into front and back ends. The temp table is in the
front end. Each time a new report is run, the table is automatically deleted
by the new create table query that runs. There are no security reasons to
require the data to be removed from the table.

I would like to know what the several reasons are for this being "not a good
design". I've spent a good deal of time and posed a number of questions
trying to solve issues with reports caused by the requirements of the
database and this was one of the solutions proposed. It certainly wasn't my
favorite option, but it was however the most suitable to make this work.

 

[Duane Hookom]

"Each time a new report is run, the table is automatically deleted
by the new create table query that runs." So, your specifications suggest
the table is deleted twice. Once after the report closes and again when a new
report is run. I'm not sure that is the best idea. I would probably delete
all records and append new ones. This allows you to use indexes on the
reporting tables.

If you want a more robust solution, you could create a new temporary MDB to
store your temporary tables. You need to be a little concerned about bloat
caused by creating and deleting objects in your front end.

--
Duane Hookom
Microsoft Access MVP

The database is split into front and back ends. The temp table is in the
front end. Each time a new report is run, the table is automatically deleted
by the new create table query that runs. There are no security reasons to
require the data to be removed from the table.

I would like to know what the several reasons are for this being "not a good
design". I've spent a good deal of time and posed a number of questions
trying to solve issues with reports caused by the requirements of the
database and this was one of the solutions proposed. It certainly wasn't my
favorite option, but it was however the most suitable to make this work.

 

[Marshall Barton]

The database is split into front and back ends. The temp table is in the
front end. Each time a new report is run, the table is automatically deleted
by the new create table query that runs. There are no security reasons to
require the data to be removed from the table.

I would like to know what the several reasons are for this being "not a good
design". I've spent a good deal of time and posed a number of questions
trying to solve issues with reports caused by the requirements of the
database and this was one of the solutions proposed. It certainly wasn't my
favorite option, but it was however the most suitable to make this work.

Adding and removing objects is a heavy duty operation and if
anything should go wrong (power outage, etc) the database
can become corrupted.

Duane already mentioned the bloat issue, but that leads to
more frequent compact operations, which in turn requires a
robust recovery scenatio. It is simply better to design the
front end so that it only has to be compacted when you need
to refresh your queries' optimization plan or field
attributes (add a lot of records or change the design of a
backend table).

 

[Armen Stein]

The database is split into front and back ends. The temp table is in the
front end. Each time a new report is run, the table is automatically deleted
by the new create table query that runs. There are no security reasons to
require the data to be removed from the table.

I would like to know what the several reasons are for this being "not a good
design". I've spent a good deal of time and posed a number of questions
trying to solve issues with reports caused by the requirements of the
database and this was one of the solutions proposed. It certainly wasn't my
favorite option, but it was however the most suitable to make this work.

Hi Stephen,

To summarize recommendations by myself and others:

- Don't use MakeTable queries - they involve creating and deleting
objects, which can cause bloating and corruption problems. Also,
MakeTable allows Access to make assumptions about your table
structure, which might be wrong someday.

- Instead, use a consistent work table structure in permanent tables,
either in the front-end or in a separate Work database (see below).

- In the Open event of your report, delete all the records from the
work table and reload them with whatever code & queries necessary.

- When the report closes, do nothing. Just leave the records in the
work table for diagnostic purposes.

- If you're dealing with thousands of records in the work table and
you notice bloating of the database size, you can just compact and
repair the front-end more often, or get a new copy of the front-end.
OR...

== Separate Work Database ==

You can put the work tables in a separate, third database, called
something like MyApp Work.MDB, and link the work tables to your
front-end app.

You use a relinking utility to manage the links to your two databases
(data and work). You're welcome to use our free J Street Access
Relinker at:
http://www.jstreettech.com/downloads

It has a CheckAppFolder mode that automatically and silently relinks a
database if it's found in the same folder as the front-end; this is
ideal for local work databases.

Now that it's a separate database, you can compact and repair it
programmatically from the front-end using the DBEngine.CompactDatabase
method.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tony Toews [MVP]]

Private Sub Report_Close()

DoCmd.DeleteObject acTable, "Temp"

End Sub

However, when I do this I get the error that the table is still in use by
the report and therefore cannot be deleted.

You could try changing the Record Source of the report to either Null
or "", whichever works. This might not work but is worth a try.

What I did though on this problem a number of years back was to put
the code that removed the link to the temporary table in the temporary
MDB in the calling forms close event.

As others have indicated I prefer using a temporary MDB for such data.

Tony

 

[Tony Toews [MVP]]

- Instead, use a consistent work table structure in permanent tables,
either in the front-end or in a separate Work database (see below).

I found that creating a temp MDB and tables within it was very fast
process taking well under a second. Fast enough that I didn't bother
trying to do any time calculations.

I was happy that this either speeded up the reports by 10 to 100 times
as fast or made complex reports possible.

Tony

 

[Stephen @ ZennHAUS]

Yes I am concerned about bloat and have already noticed that could be an
issue.

I would love to know how to create a temporary MDB file to put the data
into. Not something I know anything about.

To clarify, I want the table deleted when the report closes but have not
been successful in getting that to happen. At the moment, the make table
query that runs from code will automatically delete the table if it already
exists. I can't simply append records to the table because the same table
is the source for multiple reports each with a different set of fields.
Just in case you're thinking "but that means you can only run one report at
a time", yes that is true, but to compensate for lack of user skill, all
forms and reports are modal preventing any two objects being open or used at
the same time and requiring the user to close the foreground object before
being able to continue. This was one of the design goals and has been done
intentionally.

So i guess if a temporary MDB file can be created to store the temporary
data in it to feed the reports that would be a better solution to prevent
bloat and keep the data away from prying eyes even if data security is not
an issue in this case.

"Each time a new report is run, the table is automatically deleted
by the new create table query that runs." So, your specifications suggest
the table is deleted twice. Once after the report closes and again when a
new
report is run. I'm not sure that is the best idea. I would probably delete
all records and append new ones. This allows you to use indexes on the
reporting tables.

If you want a more robust solution, you could create a new temporary MDB
to
store your temporary tables. You need to be a little concerned about bloat
caused by creating and deleting objects in your front end.

 

[Stephen @ ZennHAUS]

Thanks Armen. All very helpful information.

To allay concerns about performance, using the make table query from code is
actually faster than running a standard interface based query to support the
report. All of the reports appear on screen more than twice as fast as they
did before.

I am going to try an build the Work DB or Temporary DB and reconstruct the
code to use it though. It seems to be a far more sensible solution that what
I have.

 

[Stephen @ ZennHAUS]

To all of the guys and gals who helped me with this problem, THANKS HEAPS!

A lot of suggestions were made and different things were tried, but in the
end I have gone with a permanent work MDB file with a number of tables that
are used for temporary data storage to drive the reports. To counter the
bloat issue, each time the main FE interface is opened and closed is
compacts and repairs the working DB which is stored in a local temp folder.

YAY!

Thanks again to all of the people who offered their assistance, in
particular Tony Toews (some of who's code I used), Armen Stein (who made the
suggestion of compacting from code) and Duane Hookom (whose suggestion it
was to use a working MDB file in the first place).