Code to drop tables and optimize database on closure

[registoni]

I am using lots of temp tables , and would like to drop them on the closure of database. Is there an elegant way of doing this automatically, and also to include option to compact and repair database? Note all my temp tables start with "temp_****".

[June7]

You can either explicitly reference each table in separate deletion actions or cycle through tables collection. Or leave the tables and just delete records.

Why do you have a lot of temp tables?

[registoni]

my numerous reports use a lot of queries (30/50 per each report and subreport) and without temp tables it was impossible to run the report or save it.
Can you show it as a sample vba code that would select all tables starting with temp_ and drop them, and then compact and save the database before closing?

[June7]

Bing: Access vba table collection
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Bing: Access vba compact on close
http://www.fmsinc.com/MicrosoftAcces...-on-close.html
http://stackoverflow.com/questions/1...atabase-in-vba

Maybe:

    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    ' Search for open AccessObject objects in AllTables collection.
    For Each obj In dbs.AllTables
        If tblList.Name Like "temp*" Then
            CurrentDb.Execute "DROP TABLE [" & tblList.Name & "]"
        End If
    Next obj

[ssanfu]

You might think about moving the temp tables to a different BE (maybe named "TempBE").
Link the temp tables to the FE. The main BE won't bloat (as much) and you can delete the records in the temp BE , compact and repair, whenever..... not need to delete the tables.