CODEWITHSUNDEEP
sqlms-access
Varun Mahajan
Varun Mahajan

Reputation: 7297

How to delete data in all ms-access tables at once?

Is there a way in MS-Access to delete the data in all the tables at once. We run a database in access, save the data every month and then delete all the data in access. But it requires deleting data from a lot of tables. Isn't there a simpler/easier way to do so?

Upvotes: 8

Views: 44107

Answers (6)

Adarsh Madrecha
Adarsh Madrecha

Reputation: 7896

This will delete all the data from all tables except from System Tables

Dim T As TableDef
DoCmd.SetWarnings False
For Each T In CurrentDb.TableDefs
    If T.Name Like "d2s_*" Then
        DoCmd.RunSQL "DELETE * FROM " & T.Name
    End If
Next T
DoCmd.SetWarnings True

Another approach: (Based on Suggestion of Christopher Duke)

Dim T As TableDef
DoCmd.SetWarnings False
For Each T In CurrentDb.TableDefs
    If Not Left(T.Name, 4) = "MSys" Then
        DoCmd.RunSQL "DELETE * FROM [" & T.Name & "]"
    End If
Next T
DoCmd.SetWarnings True

Upvotes: 0

Marcin D
Marcin D

Reputation: 916

Great answer from Alistair, although it needs to be updated. The old if statement would cause errors, and the old dynamic string wouldn't work on tables with names that have a space. It would treat a name like "person information" as "person". I've updated the code, as well as made it a little easier to add exceptions to the if statement, if you want some tables to retain their data.

 Public Sub TruncateTables()
    'Majority of code taken from a data dictionary script I can no longer source nor find the author

    On Error GoTo Error_TruncateTables

    Dim DB As DAO.Database
    Dim TDF As DAO.TableDef
    Dim strSQL_DELETE As String

    Set DB = CurrentDb()

        For Each TDF In DB.TableDefs
            If Not (TDF.Name Like "MSys*" Or TDF.Name Like "~*" Or Len(TDF.Connect) > 0) Then
                'This will prevent system, temporary and linked tables from being cleared
                strSQL_DELETE = "DELETE FROM " & "[" & TDF.Name & "]"
                DB.Execute strSQL_DELETE
            End If
        Next

    MsgBox "Tables have been truncated", vbInformation, "TABLES TRUNCATED"
    DB.Close

    Exit_Error_TruncateTables:
        Set TDF = Nothing
        Set DB = Nothing
        Exit Sub

    Error_TruncateTables:
        Select Case Err.Number
            Case 3376
                Resume Next 'Ignore error if table not found
             Case 3270 'Property Not Found
                Resume Next
            Case Else
                MsgBox Err.Number & ": " & Err.Description
                Resume Exit_Error_TruncateTables
        End Select
    End Sub

Upvotes: 10

user1958446
user1958446

Reputation: 9

Highlight all of the ROWS and then press the Delete key on your keyboard. If access is doing that thing were it doesn't let you go to the bottom,then go into a cell and click ctrl+down arrow. To highlight all rows, highlight the top row and then scroll to the bottom row and hold down shift while you select the bottom row. All rows should be highlighted.

Upvotes: 0

Alistair Knock
Alistair Knock

Reputation: 1836

Craig's answer is simple and sensible. If you really want a programmatic solution, the following VBA script will clear all the data from every table excluding the hidden tables. It requires DAO to be enabled - in Visual Basic Editor, go to Tools -> References, and tick Microsoft DAO 3.6 Object Library, then OK:

Public Sub TruncateTables()
'Majority of code taken from a data dictionary script I can no longer source nor find the author

On Error GoTo Error_TruncateTables

Dim DB As DAO.Database
Dim TDF As DAO.TableDef
Dim strSQL_DELETE As String

Set DB = CurrentDb()

    For Each TDF In DB.TableDefs
        If Left(TDF.Name, 4) <> "MSys" Then
            strSQL_DELETE = "DELETE FROM " & TDF.Name & ";"
            DB.Execute strSQL_DELETE
        End If
    Next

MsgBox "Tables have been truncated", vbInformation, "TABLES TRUNCATED"
DB.Close

Exit_Error_TruncateTables:
    Set TDF = Nothing
    Set DB = Nothing
    Exit Sub

Error_TruncateTables:
    Select Case Err.Number
        Case 3376
            Resume Next 'Ignore error if table not found
         Case 3270 'Property Not Found
            Resume Next
        Case Else
            MsgBox Err.Number & ": " & Err.Description
            Resume Exit_Error_TruncateTables
    End Select
End Sub

Upvotes: 11

ksuralta
ksuralta

Reputation: 17106

Since this is a repetitive action, it would be better if you made a simple SQL script to do this.

DELETE FROM <table1>;
DELETE FROM <table2>;
...
DELETE FROM <tablen>;

Upvotes: 0

Craig T
Craig T

Reputation: 2742

Why don't you keep an empty copy of the database on hand. At the end of the month, save the existing database, then copy the empty database in its place.

Upvotes: 16

Related Questions