Can't drop temp table

U

UpRider

I have a command button on a form that runs a report.
The code under the command button creates a table, populates it, creates a
query based on the table and assigns that query as the recordsource for the
report.
The code then opens the report in preview and then attemps to drop the
table. Access will not allow the deletion of the table complaining that it
cannot lock the table because it is in use by another user or process.
Anyone see a way around this standoff?

UpRider
 
K

Ken Snell [MVP]

Why would you want to delete the data that are being used to build/generate
the report? I think you need to provide some more details about the purpose
of what you're doing, as deleting data that are in use isn't normally a good
thing to do.
 
U

UpRider

OK, Ken, more info.

I have a shortcut menu on a form. One of the items on the menu is to print
labels for the current record. The temp table is created holding the number
of records = the number of labels required (e.g. 30 labels, 30 identical
records). After the labels are printed, I have no use for the table. I
realize that I could re-use the table and not delete it at all, just empty
it for the next label run, but I was trying to get rid of a 'baggage' table,
as it is easily created on the fly.

UpRider
 
U

UpRider

Joseph,
OK, but the code in the form's command button click event procedure is long
exited with the report still on the screen. I tried deleting the table in
the close event and the deactivate event of the report but the table was
still locked.
UpRider
 
L

Larry Linson

The code behind that button does not stop and wait for the report to run...
it opens the report and keeps right on executing, so it makes sense for the
temp table to be in use. You will have to find some way to trigger your
delete code _after_ the report. Could you do it when you close / exit the
database application?

There's no "Exit" event for the application, but one way is to keep a form
open, hidden if you like, and put the code in the Form's Close event.

BTW, you will avoid some bloating if you create the temporary table in a
temporary database, then use the Kill statement to delete the temp database.
There's more information and sample code at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP
 
K

Ken Snell [MVP]

In reading the other posts in this thread, my suggestion was tried already.

When you close the report, what happens then? As Larry Linson notes, you
need to find an event on another form that is opened after the report closes
or when the database closes to delete the table.

When I need to do something like this in my databases, I just let the table
remain, and then delete it (or delete the data from it) before I run the
report the next time. It becomes part of the normal "open report" code that
I use to run the report.
--

Ken Snell
<MS ACCESS MVP>
 
U

UpRider

Thanks to Ken, Joseph and Larry. I'll either delete it at end-of-job or
just leave it and clear it before each use....

UpRider
 
Top