在MS-Access中,有没有一种方法可以一次性删除所有表格中的数据。我们在Access中运行一个数据库,每个月保存数据,然后删除所有Access中的数据。但是这需要从许多表格中删除数据。难道没有更简单/更容易的方法吗?
为什么不保留一份空的数据库备份呢?在月底,保存现有的数据库,然后将空的数据库复制到原来的位置。
Craig的回答简单而明智。如果你真的想要一个程序化的解决方案,下面的VBA脚本将清除除隐藏表之外的所有表中的数据。它需要启用DAO - 在Visual Basic Editor中,转到工具->引用,选中Microsoft DAO 3.6 Object Library,然后点击确定:
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
来自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
请将所有行都选中,然后按下键盘上的删除键。如果Access出现了不让你到达底部的情况,请进入一个单元格,然后点击Ctrl+向下箭头。要选择所有行,请先选择顶部行,然后滚动到底部行,在选择底部行时按住Shift键。所有行都应该被突出显示。
这将删除除系统表以外的所有表中的所有数据
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
另一种方法:(基于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
由于这是一个重复性的操作,最好编写一个简单的SQL脚本来完成。
DELETE FROM <table1>;
DELETE FROM <table2>;
...
DELETE FROM <tablen>;