如何一次删除所有ms访问表中的数据？

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中是否有一种方法可以一次删除所有表中的数据。我们在访问中运行数据库,每月保存数据,然后删除访问中的所有数据。但它需要删除很多表中的数据。是不是有更简单/更简单的方法呢?

6 个解决方案

#1

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.

为什么不保留数据库的空副本。在月末,保存现有数据库,然后将空数据库复制到其位置。

#2

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

#3

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

#4

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>;

#5

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.

突出显示所有ROWS,然后按键盘上的Delete键。如果访问正在执行该操作,则不会让您转到底部,然后进入单元格并单击ctrl +向下箭头。要突出显示所有行,请突出显示顶行,然后滚动到底行,并在选择底行时按住shift。应突出显示所有行。

#6

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

#1