Delete a table on report close with slight delay

Question

I want to keep my database performance at the top, reducing the number of objects and creating stuff just temporarily. I have a table that should be automated from the code:

There is a report that needs data from that table - (works fine), so this is the flow:

'-> form is opened -> onClick find-button 
'-> table of dates is created -> table of dates is populated 
'-> Report is opened -> onReportClose (I want the table of dates to be deleted).

The code is written, it works fine on its own:

CurrentDb.TableDefs.Refresh
DoCmd.DeleteObject acTable, "temp-table"

So I added a macro on report close to run the above code: which is a function. But I get the error below:

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

I believe this is because the report is probably using it. So I added a ten seconds delay to my code:

Dim PauseTime As Variant
Dim Start As Variant
Dim Elapsed As Variant

PauseTime = 10
Start = Timer
Elapsed = 0
Do While Timer < Start + PauseTime
    Elapsed = Elapsed + 1
Loop
CurrentDb.TableDefs.Refresh
DoCmd.DeleteObject acTable, "temp-table"

Instead, It seems to be holding the whole database for 10 seconds and the same issue is still occurring. Any Ideas/suggestions on how to delete this table on report close, or force table delete in Ms-Access (maybe?) or how to work around this are welcomed.

Answer 1

You must clean the RecordSource of the Report, and run the method Requery in the Unload event. Right after that you can DROP the data table.

Private Sub Report_Unload(Cancel As Integer)

  'Desvinculo la tabla del reporte
  Me.RecordSource = ""
  Me.Requery

  'Borro las tablas de datos
  DoCmd.RunSQL "DROP TABLE name_of_table"

End Sub