Hi
I have a report based on a temp table which I would like to delete when the
report closes, using the On Close event. However, I get an error message
because Access tries to delete the table before the report has closed, which
I suppose is logical. What is the best way to achieve what I want to do?
Thanks for your help

Wez.k wrote:
>I have a report based on a temp table which I would like to delete when the
>report closes, using the On Close event. However, I get an error message
>because Access tries to delete the table before the report has closed, which
>I suppose is logical. What is the best way to achieve what I want to do?


The best thing to do is to avoid using a temp table at all.

The next best thing is to remove all the records in the
table before inserting new ones. However, this will still
cause your (front end?) database to bloat, though not as
much as deleting the whole table. Either way, the temp
table should be in a separate mdb file (see
http://www.granite.ab.ca/access/temptables.htm
for a good way to manage all that).

--
Marsh
MVP [MS Access]

Thanks for puting me on the right track Marshall, I have to use a Temp table
I'm afraid, but this should do the trick.

"Marshall Barton" wrote:

> Wez.k wrote:
> >I have a report based on a temp table which I would like to delete when the
> >report closes, using the On Close event. However, I get an error message
> >because Access tries to delete the table before the report has closed, which
> >I suppose is logical. What is the best way to achieve what I want to do?
>
>
> The best thing to do is to avoid using a temp table at all.
>
> The next best thing is to remove all the records in the
> table before inserting new ones. However, this will still
> cause your (front end?) database to bloat, though not as
> much as deleting the whole table. Either way, the temp
> table should be in a separate mdb file (see
> http://www.granite.ab.ca/access/temptables.htm
> for a good way to manage all that).
>
> --
> Marsh
> MVP [MS Access]
>

Sorry to trouble you again Marshall, but I still have the same problem, which
is that I would like to delete the temp Db when the report closes, but Access
tries to delete the Db before it fully closes the report and this gives an
error. I am using the report's OnClose event to do this. Is there another
way?

"Wez.k" wrote:

> Hi
> I have a report based on a temp table which I would like to delete when the
> report closes, using the On Close event. However, I get an error message
> because Access tries to delete the table before the report has closed, which
> I suppose is logical. What is the best way to achieve what I want to do?
> Thanks for your help

Why not just leave the temp table there until you close the database? If that
isn't a good solution, then you would need to have something else in your
database check to see if the report has closed and if it has delete the temp table.

I don't know of any way to do this in the close event of the report.

Wez.k wrote:
>
> Sorry to trouble you again Marshall, but I still have the same problem, which
> is that I would like to delete the temp Db when the report closes, but Access
> tries to delete the Db before it fully closes the report and this gives an
> error. I am using the report's OnClose event to do this. Is there another
> way?
>
> "Wez.k" wrote:
>
> > Hi
> > I have a report based on a temp table which I would like to delete when the
> > report closes, using the On Close event. However, I get an error message
> > because Access tries to delete the table before the report has closed, which
> > I suppose is logical. What is the best way to achieve what I want to do?
> > Thanks for your help

Hi

In the report "ON Deactivate" Event delete the table e.g.

DoCmd.DeleteObject acTable, "Temp"

You will need to be careful that table "Temp" exists when you next run the
report.

Regards John

"John Spencer (MVP)" wrote:

> Why not just leave the temp table there until you close the database? If that
> isn't a good solution, then you would need to have something else in your
> database check to see if the report has closed and if it has delete the temp table.
>
> I don't know of any way to do this in the close event of the report.
>
> Wez.k wrote:
> >
> > Sorry to trouble you again Marshall, but I still have the same problem, which
> > is that I would like to delete the temp Db when the report closes, but Access
> > tries to delete the Db before it fully closes the report and this gives an
> > error. I am using the report's OnClose event to do this. Is there another
> > way?
> >
> > "Wez.k" wrote:
> >
> > > Hi
> > > I have a report based on a temp table which I would like to delete when the
> > > report closes, using the On Close event. However, I get an error message
> > > because Access tries to delete the table before the report has closed, which
> > > I suppose is logical. What is the best way to achieve what I want to do?
> > > Thanks for your help
>

Wez.k,

Instead of deleting the entire table, you could use a delete query to delete
all the records in the temp table. I run a delete query when I close the
form that is calling the report.

Just a thought,
Debbie


"Wez.k" <(E-Mail Removed)> wrote in message
news:082C2DA3-5627-4518-97C0-(E-Mail Removed)...
| Sorry to trouble you again Marshall, but I still have the same problem,
which
| is that I would like to delete the temp Db when the report closes, but
Access
| tries to delete the Db before it fully closes the report and this gives an
| error. I am using the report's OnClose event to do this. Is there
another
| way?
|
| "Wez.k" wrote:
|
| > Hi
| > I have a report based on a temp table which I would like to delete when
the
| > report closes, using the On Close event. However, I get an error
message
| > because Access tries to delete the table before the report has closed,
which
| > I suppose is logical. What is the best way to achieve what I want to
do?
| > Thanks for your help