Macro to empty tables (1 Viewer)

[PRTP]

Hi

I'm wanting a macro that will erase the contents of 12 tables (but NOT delete the tables) in one go i.e. I don't want the user to have to click 12 delete queries or have 12 delete queries visible in the Object Navigation Pane. I instead want one macro that will do it all.

In the macro actions menu, I couldn't see an option to clear contents/delete all records of a table.

So I wrote some VBA along the lines of currentdb.execute "delete * from sometable", repeating that for those 12 tables.

How can I get a shortcut in the Object Navigation Pane that you can click to run that code, just like you can run an ordinary macro with a double-click?


Thanks

 

[Insane_ai]

Use the RunCode action in a macro

 

[PRTP]

Thanks Insane_ai

I eventually got this to work using the RunCode. One of my mistakes was I had the VBA written as a Sub instead of a Function

 

[Mark_]

Is this for testing or is this intended for use in an production environment?

 

[Pat Hartman]

You already had the code you needed. It just wasn't in the correct event.

There is no reason to use a macro. The user will need a "button" to run the procedure so simply put the code to run the delete queries in the click event of the button. A macro just moves the code outside of the form and makes the logic harder to follow since you have two places to look and macros are much harder to read than VBA when you are trying to document the database.

Also, keep in mind that Access cannot recover the space freed up by deleting data from a table so constantly deleting and then appending rows to the now empty tables will cause database bloot. Depending on the size of the data involved, the growth of the Access database can be quite alarming and you will constantly have to be wary of the need to compact the database so that Access can reclaim the "empty" space and reduce the size of the database.

Tell us what you are trying to accomplish. There is probably a better solution.

BTW, Make table queries are not the solution. They cause the same bloat as the delete/append method.