Deleting Records across Multiple Tables

[Discus Coach]

First time post, so be nice!

Am using Access 2013 to manipulate data across multiple tables. The tables all have differing layouts and fields as they are coming direct from the customers. Files are received from customers on a weekly basis, so I have to delete the existing contents prior to importing. I wish to delete all the contents of all these tables with a single query/module/VBA command. I can set up delete queries for each individual table and running these as a macro, but was hoping that there could be a speedier solution.
Your help and support would be appreciated.

 

[plog]

As nice as possible--you've made a bad assumption and not really looking at what you really need.

What you really need is a database without data in your tables, so...Do your deletions, make a copy of that data-free database, rename it "TemplateDatabase.mdb" and then every month make a copy of the TemplateDatabase.mdb and use that to import the new data into.

Never delete data again, just copy the template.

 

[cyanidem]

Or, if you insist on deleting:

Private Sub btnTotalDestruction_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        ' === don't touch system and temporary tables!!! ===
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            DoCmd.RunSQL "DELETE FROM " & tdf.Name & ";"
        End If
    Next
    Set tdf = Nothing
    Set db = Nothing
End Sub

But honestly plog's way is much better.

 

[Discus Coach]

Hadn't thought of it like that - many thanks!