Delete temporary table on report close

[weckels]

I have a report that gets its data from a table. When I close the report I want to delete the table. If I use DoCmd.DeleteObject acTable, TableName in the report close or deactivate event it tells me I can't lock the table because it is in use. Any thoughts? Thank you!

Will

 

[smedvid]

I assume a form calls the report. After returning from the report call, then do your delete. Something to consider is to keep the database table and just do a delete *.

htwh Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[weckels]

Steve,
Thanks for the reply. Yes, the report runs from a form with a DoCmd. There is a button with a click event that opens the report. If I put the deleteobject after the open report command it tries to delete the table right away after the report loads. On what form event should I put the docmd.deleteobject? I tried using the gotfocus event on the form and nothing happened. Some background info - I use a make-table query to make the table and the NOW function to name the table so the name is always different. I store the name in an invisible field on the form. This is to allow multiple users to have the report open at the same time.
Thanks,

Will

 

[smedvid]

Ok... what about on the close event or unload event of the form? Once the form is being cloded, the report file is no longer needed I assume. Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[weckels]

Steve,
The form has other buttons that do the same thing. There could be six tables or more that need deleted. I could add more invisible fields to store the other table names and do if-thens to delete them with the close event. This could get ugly when I add more reports to the form but this might be the only way.
Thanks,
Will

 

[smedvid]

Not a big fan of invisible fields, but they must be used in some cases...

Consider having a database table that stores the user name (assuming u have a login page) and the list of temporary tables that are created. Then on the Unload/Close event, loop through that table and clear all the temp tables associated with that user. just a thought. Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...