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?
--
Thank you,
Del

What event are you putting the code into?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Del" <(E-Mail Removed)> wrote in message
news:55FAAD4F-8483-4667-8336-(E-Mail Removed)...
> 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?
> --
> Thank you,
> Del

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


"Douglas J. Steele" wrote:

> What event are you putting the code into?
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Del" <(E-Mail Removed)> wrote in message
> news:55FAAD4F-8483-4667-8336-(E-Mail Removed)...
> > 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?
> > --
> > Thank you,
> > Del
>
>
>

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
http://I.Am/DougSteele
(no private e-mails, please)


"Del" <(E-Mail Removed)> wrote in message
news:BBEECDD4-CF47-4DDC-9B8E-(E-Mail Removed)...
> 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
>
>
> "Douglas J. Steele" wrote:
>
>> What event are you putting the code into?
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Del" <(E-Mail Removed)> wrote in message
>> news:55FAAD4F-8483-4667-8336-(E-Mail Removed)...
>> > 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?
>> > --
>> > Thank you,
>> > Del
>>
>>
>>

"Del" <(E-Mail Removed)> wrote in message
news:55FAAD4F-8483-4667-8336-(E-Mail Removed)
> 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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thank you. I'll give this a try.
--
Thank you,
Del


"Dirk Goldgar" wrote:

> "Del" <(E-Mail Removed)> wrote in message
> news:55FAAD4F-8483-4667-8336-(E-Mail Removed)
> > 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>