M Access - 删除所有表、查询、窗体、报表和模块

Question

出于安全原因，我想在我的数据库中创建一个自毁按钮。我需要修改我的代码，以便它可以循环我所有的表、查询、窗体、报表、模块并删除它们。如果发生错误，请移至下一个错误。

现在我使用以下代码，这是一个示例：

 On error Resume Next
 DoCmd.DeleteObject acTable, "tblExtra"
 DoCmd.DeleteObject acTable, "tblFinances"
 DoCmd.DeleteObject acTable, "tblHealth"
 ....

如您所见，我正在对要删除的每个项目使用 DoCmd.DeleteObject。

谢谢。

Answer 1

此代码将删除所有对象，在此过程中可能会出现错误，因此如果需要，您仍应使用错误恢复。 它关闭对象，然后删除：

Dim obj As AccessObject
For Each obj In CurrentProject.AllReports
    Debug.Print "Deleting " & obj.Name
    DoCmd.Close acReport, obj.Name, acSaveNo
    DoCmd.DeleteObject acReport, obj.Name
Next
For Each obj In CurrentProject.AllForms
    Debug.Print "Deleting " & obj.Name
    DoCmd.Close acForm, obj.Name, acSaveNo
    DoCmd.DeleteObject acForm, obj.Name
Next
For Each obj In CurrentData.AllTables
    If obj.Name Not Like "MSys*" then
         Debug.Print "Deleting " & obj.Name
         DoCmd.Close acTable, obj.Name, acSaveNo
         DoCmd.DeleteObject acTable, obj.Name
    End If
Next
For Each obj In CurrentData.AllQueries
    Debug.Print "Deleting " & obj.Name
    DoCmd.Close acQuery, obj.Name, acSaveNo
    DoCmd.DeleteObject acQuery, obj.Name
Next

供您参考，如果要添加更多要删除的对象，以下是 CurrentData 中的对象：

https://msdn.microsoft.com/en-us/library/office/ff823195.aspx

以下是当前项目中的对象：

https://msdn.microsoft.com/en-us/library/office/ff835979.aspx

Answer 2

至于Access 2016，Graham的答案在删除对象时随机失败(自动化错误(。

按

索引从最后一个到第一个删除对象更安全。checkonly 参数仅用于测试目的，以防擦除任何重要对象。

我已经在 Access 2016 MSO (16.0.8431.2110( 32 位中测试了这两种解决方案。

Public Sub deleteObjects(Optional ByVal CheckOnly As Boolean = True)
    Dim obj     As AccessObject
    Dim i       As Long
    With CurrentProject
        For i = .AllReports.count - 1 To 0 Step -1
            Set obj = .AllReports(i)
            Debug.Print "Deleting " & obj.Name
            If Not CheckOnly Then
                DoCmd.Close ObjectType:=acReport, ObjectName:=obj.Name, Save:=acSaveNo
                DoCmd.DeleteObject acReport, obj.Name
            End If
        Next
        For i = .AllForms.count - 1 To 0 Step -1
            Set obj = .AllForms(i)
            If obj.Name <> "myImportantForm" Then
                Debug.Print "Deleting " & obj.Name
                If Not CheckOnly Then
                    DoCmd.Close ObjectType:=acForm, ObjectName:=obj.Name, Save:=acSaveNo
                    DoCmd.DeleteObject acForm, obj.Name
                End If
            End If
        Next
        For i = .AllModules.count - 1 To 0 Step -1
            Set obj = .AllModules(i)
            If obj.Name <> "myImportantModule" Then
                Debug.Print "Deleting " & obj.Name
                If Not CheckOnly Then
                    DoCmd.DeleteObject acModule, obj.Name
                End If
            End If
        Next
    End With
    With CurrentData
        For i = .AllQueries.count - 1 To 0 Step -1
            Set obj = .AllQueries(i)
            Debug.Print "Deleting " & obj.Name
            If Not CheckOnly Then
                DoCmd.Close ObjectType:=acQuery, ObjectName:=obj.Name, Save:=acSaveNo
                DoCmd.DeleteObject acQuery, obj.Name
            End If
        Next
        For i = .AllTables.count - 1 To 0 Step -1
            Set obj = .AllTables(i)
            If Not obj.Name Like "MSys*" Then
                Debug.Print "Deleting " & obj.Name
                If Not CheckOnly Then
                    DoCmd.Close ObjectType:=acTable, ObjectName:=obj.Name, Save:=acSaveNo
                    DoCmd.DeleteObject acTable, obj.Name
                End If
            End If
        Next
    End With
End Sub