VBA to open back-end and remove tables

[AConrad00]

Hello,

I need to do two things to my back-end DB. I need to remove the tables, and run a compact/repair to stop the annoying "this table will be deleted..." and to keep the filesize down since just a dual-run can cause the 2gb limit to be reached.

1 - From my front-end, access the back-end database and remove all tables, and then run a compact/repair

2 - if this is easier, I'd like to delete the backend database and then create a new version of it with the same name.

Any help? I am new to the VBA game, although can fiddle through a bit.

Thanks,


On a side note, I was able to clear the tables using the EXECUTE DELETE FROM function but have no been able to find a way to delete the actual tables/objects

 

[mdlueck]

I am not certain you can automate such. That involves opening / closing the actual database... the compact operation that is.

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948

When you run compact, Access closes the database, reopens it, and fires the Autoexec macro if you have one configured... this I saw to be sure to hold the shift key to prevent it from executing, which adjusts the filesize, so you would never get out of the cleanup batch process!

Perhaps you could produce a clean version of a skeleton DB and simply copy that "template" before beginning to use it?

 

[AConrad00]

I am not certain you can automate such. That involves opening / closing the actual database... the compact operation that is.

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB

When you run compact, Access closes the database, reopens it, and fires the Autoexec macro if you have one configured... this I saw to be sure to hold the shift key to prevent it from executing, which adjusts the filesize, so you would never get out of the cleanup batch process!

Perhaps you could produce a clean version of a skeleton DB and simply copy that "template" before beginning to use it?

Thanks Micheal for the input

Minus the compact/repair, would you be able to point me in the right direction for the VBA code to perform either option? I am finding tons of hits in my research, but it references older code that I don't know how to change for Access 2010.

 

[Thales750]

I would also consider using MySQl for the tables

 

[boblarson]

I would also consider using MySQl for the tables

Or SQL Server Express 2008 would also work.

And to delete the tables you use a DROP TABLE command within DAO.

But you should be able to avoid the Messages about it being deleted by using the Execute method from DAO.

 

[mdlueck]

would you be able to point me in the right direction for the VBA code to perform either option?

Please provide context? I can not follow from the discussion thread what two options you are referring to.

 

[boblarson]

If you want to look at something look at my backend auto updater. It has the code in it that should give you everything you need. You don't need to use the tool (that is simply something I did to be able to send to someone to update their backend if I couldn't access it directly).

Backend Auto Updater

Documentation