Dropping a table from a report

[Mac]

Dear All,

I have a number of reports based on queries that are sufficiently
complex that the quickest way to run the reports is to turn the base
queries into make table queries and have the report create the table
it draws it's information from when it opens and run the report of the
table. This I can do with a simple docmd.runsql statement in VBA, what
I now need to do is delete the table, I know it can be done by
attaching a form to the report and getting the form to delete the
table, but this is impractical for these reports, is it possible for
the report to delete these tables through VBA as part of the On Close
event?

TIA

Mac

 

[Allen Browne]

It is possible to delete a table, e.g.:
dbEngine(0)(0).Execute "DROP Table1;", dbFailOnError
I'm not sure if you will run into problems in Report_Close if the report is
still using that table. It might be easier to do this next time you run the
report (in Report_Open.)

A better solution might be to set up the table the way you need it, and
populate it with an Append query instead of a MakeTable. Then use a Delete
query to clear it rather than dropping it. The code in Report_Open
Dim db As DAO.Database
Dim strSql As String
Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError
strSql = "INSERT INTO ...
db.Execute strSql, dbFailOnError

For the reasons why I prefer Execute to RunSQL, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Mac]

Dear Allen,

Many thanks, I had considered the option of deleting the data rather
than the table but I wasn't sure if I would run into the same locking
problems. I'll try the approach you've suggested. Once again, many
thanks.

Regards

Mac