Can I automate deletion of temp tables?

[Chris Ennis]

Hi All,

I have several temp tables that are generated throughout the day. I would
like to automate the deletion of these tables prior to database compaction.
The temp tables will all be prefixed by the letter z.

Does anyone know of a way that this can be done? Even if you just have an
inkling of an idea of how someone might go about this I would highly
appreciate the input.

Thanks in advance,
Chris

 

[Dirk Goldgar]

Hi All,

I have several temp tables that are generated throughout the day. I
would like to automate the deletion of these tables prior to database
compaction. The temp tables will all be prefixed by the letter z.

Does anyone know of a way that this can be done? Even if you just
have an inkling of an idea of how someone might go about this I would
highly appreciate the input.

'*** Untested code ***

Dim db As DAO.Database
Dim strTable As String
Dim t As Long

Set db = CurrentDb

With db.TableDefs
For t = (.Count - 1) to 0 Step -1
strTable = .Item(t).Name
If Left(strTable, 1) = "z" Then
Debug.Print "Deleting " & strTable
.Delete strTable
End If
Next t
End With

Set db = Nothing

 

[Chris Ennis]

Thanks for the rapid response Dirk. Would something like this be altered to
suit my environment and then run as a .bat file?

 

[Douglas J. Steele]

What Dirk gave you runs inside of Access, not in a bat file. No changes are
required to that code at all: it does exactly what you asked for (it loops
through all of the tables in your database, deleting those whose names start
with z)

Another option would be to put your temporary tables in a temporary
database, so that you could just get rid of the temporary database instead.
Tony Toews has an example of this at
http://www.granite.ab.ca/access/temptables.htm

 

[Dirk Goldgar]

Another option would be to put your temporary tables in a temporary
database, so that you could just get rid of the temporary database
instead. Tony Toews has an example of this at
http://www.granite.ab.ca/access/temptables.htm

That's a good suggestion, Doug. Chris, if you decide to try this, I
have a class object that encapsulates the functionality Tony
demonstrates on that page.

 

[Dirk Goldgar]

Thanks for the rapid response Dirk. Would something like this be
altered to suit my environment and then run as a .bat file?

I don't really follow you. That code must run inside Access. You could
execute it from the Close event of a form that remains open while your
database is open, so whenever the database is closed, the tables are
deleted. Or, if you only want to do this as part of a compaction
process, you could write a function that calls this code and then
compacts the database. If you want to be able to invoke this function
as part of a .bat file, you could create a macro that calls it, and have
the .bat file start Access with a command line that opens the database
and calls the macro (using the /x command-line argument).

 

[Fred Boer]

Dear Dirk:

Would you be willing to send me a copy of that "class object"? I'd be
interested in looking at it; I've only seen one example of a class object in
my VBA textbook...

Thanks!
Fred

 

[Chris Ennis]

Dirk,
I half expected to have to do some of my own legwork on this. I didn't
realize you had offered me up everything on a platter. Access is fairly new
to me as I've been working in an SQL environment for years, I honestly feel
like a dope but I get lost in the small things.

I think I am going to try and follow your suggestion of writing a function
that calls the code you provided and then compacts the database. Thanks to
all who provided excellent feedback. It is much appreciated.

-Chris

 

[Dirk Goldgar]

Dear Dirk:

Would you be willing to send me a copy of that "class object"? I'd be
interested in looking at it; I've only seen one example of a class
object in my VBA textbook...

Fred -

I'm really busy right now, but I'll send it off to you as soon as I have
a moment.