SQL 如何一次删除所有ms-access表中的数据？

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

提问by Varun Mahajan

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?

MS-Access 中有没有办法一次性删除所有表中的数据。我们在访问中运行一个数据库，每个月保存数据，然后删除所有访问中的数据。但它需要从很多表中删除数据。没有更简单/更容易的方法吗？

回答by Craig T

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.

为什么不手头保留一份数据库的空副本。在月底，保存现有数据库，然后将空数据库复制到其位置。

回答by Alistair Knock

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:

克雷格的回答简单而明智。如果你真的想要一个程序化的解决方案，下面的 VBA 脚本将清除每个表中不包括隐藏表的所有数据。它需要启用 DAO - 在 Visual Basic 编辑器中，转到工具 -> 引用，然后勾选 Microsoft DAO 3.6 对象库，然后确定：

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

回答by Marcin D

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.

Alistair 的出色回答，尽管它需要更新。旧的 if 语句会导致错误，旧的动态字符串不适用于名称带有空格的表。它将像“个人信息”这样的名称视为“人”。如果您希望某些表保留其数据，我已经更新了代码，并使向 if 语句添加异常变得更容易一些。

 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

回答by Adarsh Madrecha

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

回答by user1958446

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.

突出显示所有行，然后按键盘上的 Delete 键。如果访问正在做那件事，它不会让你进入底部，然后进入一个单元格并单击 ctrl+向下箭头。要突出显示所有行，请突出显示顶行，然后滚动到底行并在选择底行时按住 shift。所有行都应突出显示。

回答by ksuralta

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

由于这是一个重复操作，如果您制作一个简单的 SQL 脚本来执行此操作会更好。

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