Macro to delete table

[tracysdfw]

In Access 2010 macros, what are commands to delete a table. It was available in prev versions, but I cannot find "delete table"

 

[PNGBill]

I just checked and can find no way to Delete a Table.
I also can not think of a time when I would want the system to delete a table.

Why do you want a table deleted by the system ?

If the database is Split the Back End, tables, ussually have the least developer attention ie, Tables are not deleted and created as a normal daily event.

Temp Tables can be used but they will over write themselves.

 

[cookiegary01]

Tracy,
Have you tried to create a Delete Table Query?
In your Macro, you can then run this Query.

 

[tracysdfw]

Query is the answer! Somtimes we just have to get back to basics. I will import or link table, rename it so i can run append or update qry, then delet table.

 

[PNGBill]

How do you Delete a Table with a qeury ? You can delete records but the table still remains.

 

[Mile-O]

At its simplest, this will work:

Public Sub DeleteTable(ByVal strTableName As String)
     CurrentDb.TableDefs.Delete strTableName
End Sub

Though why you'd want to delete a table is a concern I'd echo.

 

[MrChips]

Mile-O

I can't answer for previous posts, but in my instance i wish to import an updated table into my database containing up-to-date base information I need.
If I don't delete the existing table, access doesn't overWrite the old 'PGR' table it numbers it 'PGR-2' which will not be recognised by my existing VBA.

It was available in MAcros at some point and when I went to 2010 it still worked, but having since deleted the line for other reasons, I can't reinstate it. Most annoying!

I am not an expert, and would appreciate any help/advice on how I could do this within a Macro.

 

[RainLover]

I would suggest you join the real world of Database programing and learn VBA.

Stop using Macros now while your needs are not so complicated.

VBA can do much much more than simple Macros.

 

[MrChips]

I and agree and accept that VBA is a valid alternative to using Macros, but in some cases, in the past, I have been known to 'reinvent the wheel' in VBA without letting native Access processes take the strain.

I still wonder why the deleteObject has been removed from the Macros ... I can't see that it is 'dangerous' in any way.

Any thoughts?

P.S. I have actually solved my problem by using VBA in a module to delete the offending table, and have called it form the macro with a runCode command. Works fine ... but I still wonder why the DeleteObject is no more ... just like the dead parrot really.

 

[RainLover]

I don't know for sure but I believe Microsoft has plans to turn Access into a Web based application.

They are changing various things in each version as part of their long term plan.

I would advise that even though this site and google are very helpful, it is advisable to get into the habit of searching Help in Access. You may not always find the answer and sometimes if you do you simply can't understand it. You will however develop the skill of using help and learn a lot of things that you never thought of.

One other thing. Deleting Tables can cause your DB to bloat. Grow in size. So a compact and repair on the back end from time to time could be warranted. Always do a Back up First.

Good Luck with the project.

 

[MrChips]

Thank you ... especially for your advice on 'bloat'.
I will do as you suggest ... thank you

 

[RainLover]

Thank you ... especially for your advice on 'bloat'.
I will do as you suggest ... thank you

Don't forget that Back Up first. The last thing you want to do is to destroy your data.

The chances of corruption are very low, but better be sure than sorry.

 

[MrChips]

Thnaks again.

Having been 'caught' before with inadequate back-up, I now do a full back-up each evening to c:/ a USB drive and a remote back-up site.

The pain still lingers heavily in my mind....

 

[RMD]

The Delete macro still exists but microsoft has hidden it. Copy the 8 lines below and past into the macro. I had to remove the 'H' after xmlns =" in order to post.

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="ttp://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><Action Name="DeleteObject"><Argument Name="ObjectType">Table</Argument><Argument Name="ObjectName">User List All</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>

 

[MrChips]

Crumbs .... what a mouthfull.
It is so complicated that I would be worried to even try it.
Thank you, however, for posting your solution. It is appreciated.

 

[RMD]

I just noticed that the code above is not displaying correctly. Again you must add the "h" back into the address.

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="ttp://schemas.microsoft.com/office/accessservices/2009/11/application">
<UserInterfaceMacro MinimumClientDesignVersion="14.0.0000.0000">
<Statements>
<Action Name="DeleteObject">
<Argument Name="ObjectType">Table</Argument>
<Argument Name="ObjectName">User List All
</Argument></Action>
</Statements>
</UserInterfaceMacro></UserInterfaceMacros>

 

[FFDiz]

Go to the Design Tab
Click on Action Catalogue
Click on Show all Actions
select Database objects to expand command tree
it will be in there with an exclamation point next to it!!!