Deleting table in Access 2000

[Guest]

My code creates a table for a report to use as its Record Source and I want
to delete the table when the report is closed. I have tried the following
code:

DoCmd.DeleteObject acTable, "My Table",

but I get an error stating that the database engine can not lock the table
because it is in use. When I try to remove the table as the report's Record
Source a message tells me that I can not set the Record Source after printing
has started, even if I'm not printing the report.

How can I delete this table?

 

[Douglas J. Steele]

What event are you putting the code into?

 

[Guest]

I've put it on Report_Close() first and then tried it on Form_Current() event
of the form that would receive the focus once the report was closed.
--
Thank you,
Del

What event are you putting the code into?

 

[Douglas J. Steele]

To remove it in the form, you'd have to open the report in design mode. That
may be your only alternative though.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I've put it on Report_Close() first and then tried it on Form_Current()
event
of the form that would receive the focus once the report was closed.

 

[Dirk Goldgar]

My code creates a table for a report to use as its Record Source and
I want to delete the table when the report is closed. I have tried
the following code:

DoCmd.DeleteObject acTable, "My Table",

but I get an error stating that the database engine can not lock the
table because it is in use. When I try to remove the table as the
report's Record Source a message tells me that I can not set the
Record Source after printing has started, even if I'm not printing
the report.

How can I delete this table?

If you're opening the report in preview, you could have your code loop
after opening the report, until the report is closed. Like this:

DoCmd.OpenReport "MyReport", acViewPreview

Do While CurrentProject.AllReports("MyReport").IsLoaded
DoEvents
Loop
DoCmd.DeleteObject acTable, "MyTable"

I'm not sure about a couple of things:

1. I don't know if this works for reports that are opened for
printing, rather than for preview.

2. I don't know whether you might have to first loop until the
report *is* loaded, and only then loop until it isn't.

But you can check those things out.

 

[Guest]

Thank you. I'll give this a try.