Delete Tables w/out Opening the Database?

[Greg_W via AccessMonster.com]

Hi all,

I am developing a process for copying (backing up) tables from the main
database into an archive database. For this, I use a simple Make-Table query
definition and the execute command in VBA. The VBA code tags today's date
onto the end of each archived table, giving each added table a unique name.
It works great.

However, I also need a way to purge out the older tables from that archive
database, for example, all tables that were created longer than six months
ago. Otherwise the archive db would gradually grow to monstrous sizes (or
maybe that would never be a problem, what do you think?).

I figure I have to open the database before I can purge the older tables. I
use the following code to do this:
'=========================================================

Dim apl As Access.Application
Dim tbl As TableDef
Dim db As DAO.Database

Set apl = CreateObject("Access.Application")

apl.OpenCurrentDatabase FilePath:="D:\TEST_Archive.mdb", Exclusive:=True
apl.Visible = False
Set db = apl.CurrentDB
'Code to purge the unwanted tables goes here.

'=========================================================

The problem I have, however, is that the Security Warning pops up, which
requires user input and defeats the whole purpose of making this an automated
process.

Is there a way to accomplish this without opening the database? Or is there a
way to automatically bypass the security warning through VBA?

 

[Piet Linden]

Hi all,

I am developing a process for copying (backing up) tables from the main
database into an archive database. For this, I use a simple Make-Table query
definition and the execute command in VBA.  The VBA code tags today's date
onto the end of each archived table, giving each added table a unique name.
It works great.

However, I also need a way to purge out the older tables from that archive
database, for example, all tables that were created longer than six months
ago. Otherwise the archive db would gradually grow to monstrous sizes (or
maybe that would never be a problem, what do you think?).

I figure I have to open the database before I can purge the older tables.I
use the following code to do this:
'=========================================================

Dim apl As Access.Application
Dim tbl As TableDef
Dim db As DAO.Database

Set apl = CreateObject("Access.Application")

apl.OpenCurrentDatabase FilePath:="D:\TEST_Archive.mdb", Exclusive:=True
apl.Visible = False
Set db = apl.CurrentDB
'Code to purge the unwanted tables goes here.

'=========================================================

The problem I have, however, is that the Security Warning pops up, which
requires user input and defeats the whole purpose of making this an automated
process.

Is there a way to accomplish this without opening the database? Or is there a
way to automatically bypass the security warning through VBA?

Maybe it's just me, but I can understand deleting older *records*, but
older *tables*? Sounds like your database structure is a bit odd.
Did you try doing something like

CurrentDB.Execute "DROP TABLE TableName...", dbFailOnError

 

[Douglas J. Steele]

I share your puzzlement, but remember that Greg wants to delete from an
external database.

Dim db As DAO.Database

Set db = OpenDatabase("D:\TEST_Archive.mdb")
db.Execute "DROP TABLE TableName...", dbFailOnError
Set db = Nothing

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)



Maybe it's just me, but I can understand deleting older *records*, but
older *tables*? Sounds like your database structure is a bit odd.
Did you try doing something like

CurrentDB.Execute "DROP TABLE TableName...", dbFailOnError