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

Posted on 2010-01-07
Last Modified: 2013-11-28
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!
Question by:ltdanp22
    LVL 29

    Expert Comment

    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)


    LVL 22

    Expert Comment

    by:Kelvin Sparks
    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.
    LVL 92

    Accepted Solution

    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 Like "tmp*" Then .RunSQL "DELETE * FROM [" & & "]"
                        .SetWarnings True
                    End With
                End If



    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now