Delete Table on Report_Close

[nhabegger]

I am showing my users a report (rptAnimations) which displays the data in a table (tblAnimationReport). I want to delete the table on Report_Close. I've tried using DoCmd.DeleteObject, but I get the following message:

Run-time Error '3211':
The database engine could not lock table 'tblAnimationReport' because it is already in use by another person or process.

Here is my code:

Private Sub Report_Close()
 
  DoCmd.DeleteObject acTable, "tblAnimationReport"

End Sub

The tblAnimationReport is created using a Make Table query. Here is the code related to the control which runs that query and displays the report off a button control on a form.

Sub Command9_Click()

  DoCmd.OpenQuery "qryMakeAnimationReport", acViewNormal, acEdit
    
  DoCmd.Close acForm, "frmMakeAnimationReport"
  
  DoCmd.OpenReport "rptAnimationReport", acViewPreview
  
End Sub

Let me know if you need more information. Thanks in advance.

[ajetrumpet]

you won't be able to do it. deleting a table requires the data not be in use anywhere else. the data is in use until all the report's events are bypassed. thus, "on-close" means the data is still being used

[jzwp11]

You could delete the old table before creating the new one. So you would put the delete command before the make table command.

[nhabegger]

Thanks for the replies.

I think I may be able to solve this by using two control buttons on my form. The first will allow the user to view the report using:

 DoCmd.OpenReport "rptAnimationReport", acViewPreview

The second button will print the report using:

 DoCmd.OpenReport "rptAnimationReport", acViewNormal

Once the report is printed, I can delete the table. I will also delete the table during the On_Close event of the form, which will allow me to get rid of the table if the user only views the report.

Thanks again for the input, everyone!

[jzwp11]

What happens if the user closes the for while the report is still open? I don't see how that is different from your initial situation. Please let us know if your plan is successful.

[pbaldy]

Generally speaking, it would be more efficient to delete records from and then add records to a table, rather than delete/recreate the table itself. In other words, an append query instead of a make table query.

[Access_Blaster]

nhabegger,

Check out Maximus soultion to a similar question.

https://www.accessforums.net/program...able-6291.html

He uses the forms close event, and then unbinds the record source.

Richard