How to cleanup a report's temporary tables?

[A.Trietsch]

A report creates temporary tables for its own use during the Open event.
These tables cannot be deleted during the Close event, because they cannot
be locked while "still in use" (by the report itself apparently).
- How can the tables be released by the report before attempting to delete
them?
- Is there any other mechanism to have such temporary tables automatically
cleaned up after use by the report?

 

[Maurice]

Have you tried running a delete query first before creating the in the open
event?

hth

 

[A.Trietsch]

I don't understand what you mean.
The temporary table doesn't exist before the report is at the Open event.
Only then the table is created by the report using VB code.
Thereafter the report uses the table for producing output.
After that use, I would like the temporary table to be cleaned up again.
So, in the last event the report triggers, the Close event, I try to delete
the table using VB code again. That fails while the table is not yet released
by the report itself.

 

[Marshall Barton]

I don't understand what you mean.
The temporary table doesn't exist before the report is at the Open event.
Only then the table is created by the report using VB code.
Thereafter the report uses the table for producing output.
After that use, I would like the temporary table to be cleaned up again.
So, in the last event the report triggers, the Close event, I try to delete
the table using VB code again. That fails while the table is not yet released
by the report itself.

I think Maurice is saying that you should not be creating
the table. If the table already exists, then the report's
Open event can just delete the old records and use an append
query (instead of a make table query) to add the new
records. It's not even required that the report's close
event delete the records.

 

[Maurice]

I thought you were working with an existing tmp table. If you did you could
just append everything you needed when opening the report. If you would close
the report you could empty the table again by using a delete query. If the
table is created when you open the report what is the action you are using
for it. Are you using a maketable query?

 

[A.Trietsch]

Marshall / Maurice

Now I got your point.

It is a maketable query.
The reason is that the report uses fairly complicated queries built on many
links to external databases (including .dbf).
In particular when similar queries are simultaneously used in associated
forms, this might result in Access complaining that it can't open any more
databases, or just failing to produce portions of the report.
So the number of open links is reduced by a maketable during Open, like
SELECT * INTO TempTable FROM ComplicatedQuery;
so the report can then use TempTable instead of ComplicatedQuery.
I would rather not rely on prior existence of TmpTable, so let the maketable
(silently overwriting any previous result) do the first part of the trick.
That does work and the report produces output as desired.

Now the neatest would be to let the report itself get rid of its temporary
table after use.
But if that is not possible, the next best is indeed to let its Close event
just delete all the records from that table. I'll try that.
( ... or endeavour in an auxiliary form for "post-mortem cleanup" purposes
....)

 

[Marshall Barton]

What's the problem with using:
INSERT INTO TempTable FROM ComplicatedQuery
instead of
SELECT * INTO TempTable FROM ComplicatedQuery

If you rally have a problem using an append query, can you
let the table hang around after the report closes and delete
in in the open event just before running the make table
query? Unless there is a lot of data, it shouldn't matter.

Either way, be prepared to make a backup and Compact
frequently. Instead of bloating you database file, you
should seriously consider
http://www.granite.ab.ca/access/temptables.htm

 

[A.Trietsch]

Marshall,

What's the problem with using:
INSERT INTO TempTable FROM ComplicatedQuery

This assumes that TempTable exists and has the same structure as
ComplicatedQuery.

instead of
SELECT * INTO TempTable FROM ComplicatedQuery

This doesn't assume anything and therefore I like it more, but that is just
a matter of style.

If you rally have a problem using an append query, can you
let the table hang around after the report closes and delete
in in the open event just before running the make table
query? Unless there is a lot of data, it shouldn't matter.

The maketable query has the same effect, when it is wrapped in like this:

Private Sub Report_Open(Cancel As Integer)
Dim SQL As String
' Reduce complexity of report by storing query results into temporary
tables
SQL = "SELECT * INTO TempTable FROM ComplicatedQuery;"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
....

Either way, be prepared to make a backup and Compact
frequently. Instead of bloating you database file,
you should seriously consider
http://www.granite.ab.ca/access/temptables.htm

This includes an interesting hint:

It seemed to be the kind of solution I was looking for, so I tried:

Private Sub Report_Close()
' Cleanup temporary tables created by report
Dim SQL As String
SQL = Me.RecordSource
Me.RecordSource = ""
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "TempTable"
DoCmd.SetWarnings True
Me.RecordSource = SQL
End Sub

But this apparently does not work for reports, since it results in a
run-time error 2191:
You can't set the Record Source property in print preview or after printing
has started

 

[Marshall Barton]

This assumes that TempTable exists and has the same structure as
ComplicatedQuery.
This doesn't assume anything and therefore I like it more, but that is just
a matter of style.

It's your application.

The maketable query has the same effect, when it is wrapped in like this:

Private Sub Report_Open(Cancel As Integer)
Dim SQL As String
' Reduce complexity of report by storing query results into temporary
tables
SQL = "SELECT * INTO TempTable FROM ComplicatedQuery;"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
...

This includes an interesting hint:


It seemed to be the kind of solution I was looking for, so I tried:

Private Sub Report_Close()
' Cleanup temporary tables created by report
Dim SQL As String
SQL = Me.RecordSource
Me.RecordSource = ""
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "TempTable"
DoCmd.SetWarnings True
Me.RecordSource = SQL
End Sub

But this apparently does not work for reports, since it results in a
run-time error 2191:
You can't set the Record Source property in print preview or after printing
has started

I think you are fixated on using the Close event to clean
up. I would put code in the database's starup procedure to
delete the pld temp mdb and then create a new one.

 

[Maurice]

Yep, that's how i check things also. First clean up any old data that is
still out there and the fill it up again. To much focus on the closing while
you can place it easily in the startup section... but that's just my opinion