Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

VBA fcn to delete all records from multiple tables in Access DB

I need to write a VBA fcn that deletes all the records in a database for about 15 out of 25 tables. Is there some more efficient/elegant way of doing this other than...

dbs.DoCmd.SetWarnings False
dbs.DoCmd.RunSQL "DELETE * FROM NameOfTable1"
dbs.DoCmd.RunSQL "DELETE * FROM NameOfTable2"
dbs.DoCmd.RunSQL "DELETE * FROM NameOfTable3"
dbs.DoCmd.RunSQL "DELETE * FROM NameOfTable4"
dbs.DoCmd.RunSQL "DELETE * FROM NameOfTable5"
dbs.DoCmd.RunSQL "DELETE * FROM NameOfTable15"
dbs.DoCmd.SetWarnings True

The VBA fcn will need to be executed from an Excel file (which is why dbs precedes DoCmd). Thanks!
1 Solution
I would do it with ADO instead, but you are still looking at passing 15 SQL statements (even if its in a loop).

For intCount = 1 To 15
    dbs.DoCmd.RunSQL "DELETE * FROM NameOfTable" & Cstr(intCount)


Kelvin SparksCommented:
Short of creating a list of tables and then creating a loop to cycle through them, You're probably stuck with that approach. If there was referential integrity including Cascade Delete for these and only these and there was one top table, then deleting data from that would cascade through the rest. A most unlikely scenario.
Patrick MatthewsCommented:
Hello ltdanp22,

ABout the only thing I would add is, if the tables you need to run this against all follow a common naming
convention, then yu may be able to enumerate the TableDefs collection and do the deletes that way.  I
have a routine like that in several of my projects:

            If MsgBox("Do you want to purge data from your temp tables?", vbYesNo, "Quitting") = vbYes Then
                With DoCmd
                    .SetWarnings False
                    For Each td In CurrentDb.TableDefs
                        If td.name Like "tmp*" Then .RunSQL "DELETE * FROM [" & td.name & "]"
                    .SetWarnings True
                End With
            End If


ltdanp22Author Commented:
Patrick, this is EXACTLY what I needed. Thank you!

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now