Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

ms-access

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?

like image 934
Varun Mahajan Avatar asked Mar 26 '09 09:03

Varun Mahajan


People also ask

How do I select all in a table in Access?

You can use an asterisk (*) to select all fields in a table.

Can you delete multiple records from a table?

Hello, Yes, you can. You need to create logic to validate if the record is selected and delete that record (inside of for each) or store what records are selected and do a bulk delete with SQL.


2 Answers

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.

like image 69
Craig T Avatar answered Sep 19 '22 17:09

Craig T


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
like image 42
Alistair Knock Avatar answered Sep 23 '22 17:09

Alistair Knock