Delete all tables in one shot in Ms access-CodePudding

Here is my VBA code, which tries to loop through all the tables which contains PB (PB is the first two letters of all of the tables) in their name and delete them. I get the error msg at DoCmd line

Option Compare Database
Option Explicit

Public Sub del_all_tables()
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Set db = CurrentDb

   For Each tdf In db.TableDefs
       If Not (tdf.Name Like "PB*") Then
           DoCmd.DeleteObject acTable, tdf.Name
       End If
   Next
End Sub

Edit: I would like to not to delete the tables itself but delete the records inside the tables

CodePudding user response：

Use DELETE action SQL to remove records. Use CurrentDb.Execute and don't have to worry about turning Warnings off and on.

CurrentDb.Execute "DELETE FROM [" & tdf.Name & "]".

If you want to delete records only from tables starting with PB, remove Not from the condition - otherwise you will delete from all other tables (including system tables which is bad).

Public Sub del_all_records()
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Set db = CurrentDb

   For Each tdf In db.TableDefs
       If tdf.Name Like "PB*" Then
           db.Execute "DELETE FROM [" & tdf.Name & "]"
       End If
   Next
End Sub

CodePudding user response：

First the object name in the docmd statement is causing error, because you are not calling a full object name, rather you are trying to use the like operator.

Secondly if you want to delete records and not the table, then you have to use a delete statement.

You can play around with the query design view to have a feel of how the delete statement/query in access is.

CodePudding user response：

My original code is to delete the all the tables what did not work. I took the source from here. https://access-excel.tips/access-vba-loop-through-all-tables/

But if you would like to delete all the records in multiple tables with one shot, you need to create delete queries for each table separately.

Then you can use VBA to openeach query and run with the following code:

Option Compare Database
Option Explicit

Public Function RunMyqueries()
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"


MsgBox "Done!"

End Function

In my case I have 2 queries, if you have more just continue added them.

Also, you might want to turn off the Warning everytime before deleting the records with DoCmd.SetWarnings False