Deleting table after being used in a report in the recordsource

[Sarah]

Does anyone know how can I delete a table that I use in the recordresource of
my report after the user closes the report?

Thank you.

Sarah

 

[Sarah]

No matter where I put that command in the report, I get an error saying the
table is already in use. I'm not sure which event to execute if from. Do
you know?

 

[Duane Hookom]

Isn't this thread a repeat of a previous one where Jeff was asking for
clarification?

I doubt you can delete the table from any code within the report. Live with
it. Use code in the form that calls the report to delete the table. Depending
on your version of Access this might be easy or not so easy.

 

[Sarah]

Yes this is a repeat because I did not get a suffient answer when I posted it
the first time. I did clarify and got no response after that. I thought my
chances of getting someone else to respond would be better if I posted it
again.

The problem with your solution is if I place the code to delete the table
after I call the report, it doesn't wait until the user closes the report to
execute the delete table code. It tries to do it right after you call the
report which causes an error (table in use by another user).

I'm using Access 2007.

 

[Sarah]

If it doesn't go in a report event, then how can I get to the code that
closes the report? Isn't that the On Close Event?

 

[Duane Hookom]

I think you can open the report acDialog so the code stops execution until
after the report is closed.

Other alternatives is to just leave the table and delete it prior to needing
it again.

 

[John W. Vinson]

Does anyone know how can I delete a table that I use in the recordresource of
my report after the user closes the report?

I'd suggest that you "unask" the question. It's essentially NEVER necessary to
create a new table just for the purpose of running a report! If you have a
MakeTable query, it contains a simple Select query; you can base your report
on that query.

Creating a new table, registering it into the systems tables, filling it with
data, and then deleting it are all just absolutely unnecessary overhead. In
addition, you'll bloat your database since the space it occupies will not be
freed when the table is deleted.

Why do you feel that you need to create and then delete a table just to print
a report? Is there something I'm missing?

 

[Sarah]

I have multiple users using the database and this is the function that
creates the resulting table. It's just too complex to put it into a simple
query. It was easier to create seperate tables then combine them into one
and have that be the reulting table. Back to the drawing board.....

 

[John Spencer]

In the code that calls the report, you can delete the table after the
report closes.

Your code will need to check to see if the report is loaded and loop
until the report is closed.

In the simplest form that could look like

DoCmd.OpenReport "NameOfReport"

WHILE CurrentProject.AllReports("NameOfReport").IsLoaded
DoEvents 'Need this so other processing will execute
WEND

You could make that more efficient by introducing a delay into the loop.
You might use the Sleep API (code below)

WHILE CurrentProject.AllReports("NameOfReport").IsLoaded
DoEvents 'Need this so other processing will execute
Call sSleep(2000) '2 second delay
WEND



Make code go to Sleep
Author: Dev Ashish


More than a few people have asked me if an alternative to DoEvents
exists to implement a wait period in Access. (DoEvents yields execution
so that the operating system can process other events.)

The answer is yes, the Sleep API can be used for this. Look at the
sample sTestSleep Sub. When you run it, you'll notice a delay beforethe
Msgbox appears on screen. The duration can be increased/decreased
bychanging the constant cTime to a corressponding higher/lower value.

For normal use from within your code, simply place a call to the
sSleep sub with an appropriate time in milliseconds



'***************** Code Start *******************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)

Sub sSleep(lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Sub

Sub sTestSleep()
Const cTIME = 1000 'in MilliSeconds
Call sSleep(cTIME)
MsgBox "Before this Msgbox, I was asleep for " _
& cTIME & " Milliseconds."
End Sub
'***************** Code End *********************

 

[Sarah]

John,

You're the best! I wish you would have been the one to answer my question
in the first place. You don't know how much time you've saved me. I can't
thank you enough. If I could, I would buy you a drink.

Thank you, thank you, thank you!!!!!! It worked great!!!!

 

[John Spencer]

Thanks for the feedback.

You did have two of the finest helping you - Duane Hookom and John
Vinson are very knowledgable.

One of the strengths of these forums is that many different people can
look at a problem and offer a solution.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================