Deleting a TableDef being used in a report.

[RSfromCO]

I have a complex report that contains 12 sub-reports. All the sub-reports use the same data which is generated by a rather complex set of queries.

When I tried to use the queries for the report it took forever to generate, so I took the query results and saved it to an indexed table and the report runs great.

So..., since the indexed table is only needed for the execution of this report, I decided to write the code in the Report_Open procedure to create the table and populate it with the results from the query (WHICH IS WORKING GREAT !!!). I then want to write code in the Report_Close procedure that would delete the table. There is no reason for the table to exist other than during the generation of this report.

The problem is when I try to execute the code in the Report_Close procedure...
Dim db As Database
Set db = currentDB
db.TableDefs.Delete ("my_temporary_Table&quot

I get a run-time error 3211
The database engine could not lock table "my_tempoary_table"
because it is in use by another person or process.

The process that has the table open is the REPORT that I am now trying to close. Is there a way to "close" the table so that I can delete it using code in the Report_Close procedure ?

 

[vbajock]

change
db.TableDefs.Delete ("my_temporary_Table&quot
to
db.execute "DROP TABLE my_temporary_Table"

 

[RSfromCO]

Thanks, but this just produces the same error. The table is still being held open by the Report so the DROP statement can't be executed.

 

[vbajock]

Sorry, should have read your post a little closer. Truth is, you can't do it. The DROP needs to go somewhere in your code where it gets kicked off after your report is fully closed.

 

[PHV]

And what about CREATE TEMPORARY TABLE ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[connectionscentres]

Hi RSfromCO.

I have EXACTLY the same problem as you did.

Did you manage to fix it?

 

[RSfromCO]

I did not fix it. I ended up just leaving the temporary table sitting in the database. In the Report_Open routine, I delete the table (if it exists) and create a new one. I was never able to delete the table in the Report_Close routine.

 

[VBslammer]

I would delete the table in the Report_Open() event as well, but it could also be done on the form that opens the report:

Private Sub cmdOpenReport_Click()
  Dim strReport As String

  strReport = "rptDumpTempTable"
  DoCmd.OpenReport strReport, acViewPreview
  DeleteTableAfterClose strReport

End Sub

Sub DeleteTableAfterClose(ByVal strReport As String)
On Error GoTo ErrHandler
  Dim strTempTable As String
  
  While CurrentProject.AllReports(strReport).IsLoaded
    If Len(strTempTable) = 0 Then
      strTempTable = Reports(strReport).RecordSource
    End If
    DoEvents
  Wend
  
  DoCmd.DeleteObject acTable, strTempTable
  MsgBox "Deleted: " & strTempTable
  
ExitHere:
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob