Access 刪除資料表後,實際上會將其重命名為 ~tmpxxxx 格式的暫存表並隱藏。透過 DAO 遍歷 TableDefs 集合,找出這些暫存表並重建即可恢復。
Microsoft Office 16.0 Access database engine Object Library(版本號視安裝而定)Option Explicit
'''
''' 恢復被刪除的資料表(名稱以 ~tmp 開頭的隱藏表)
'''
Public Function RecoverDeletedTables() As Long
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim sql As String
Dim recoveredCount As Long
Dim newTableName As String
Set db = CurrentDb()
recoveredCount = 0
' 遍歷所有 TableDef(包含隱藏/系統表)
For Each tdf In db.TableDefs
' 判斷是否為刪除後的暫存表
If Left$(tdf.Name, 4) = "~tmp" Then
' 新表名:去掉前綴 ~tmp
newTableName = Mid$(tdf.Name, 5)
' 構建建表查詢:SELECT INTO 新表
sql = "SELECT * INTO [" & newTableName & "] FROM [" & tdf.Name & "];"
' 關閉警告訊息(如「將新增 N 筆記錄」)
DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True
recoveredCount = recoveredCount + 1
MsgBox "已恢復資料表: " & newTableName, vbInformation, "恢復成功"
End If
Next tdf
If recoveredCount = 0 Then
MsgBox "未發現可恢復的已刪除資料表。", vbExclamation, "提示"
End If
RecoverDeletedTables = recoveredCount
ExitProc:
Set db = Nothing
Exit Function
ErrHandler:
DoCmd.SetWarnings True ' 確保恢復警告
MsgBox "發生錯誤: " & Err.Description, vbCritical, "錯誤"
Resume ExitProc
End Function
.accdb 檔案modRecoverTable)?RecoverDeletedTables() 執行' 測試前準備:建立兩張表並刪除
Sub TestSetup()
Dim db As DAO.Database
Set db = CurrentDb()
' 建表
db.Execute "CREATE TABLE T1 (ID LONG PRIMARY KEY, Name TEXT(50))"
db.Execute "CREATE TABLE T2 (Code TEXT(10), Val DOUBLE)"
' 插資料
db.Execute "INSERT INTO T1 VALUES (1, 'Alpha'), (2, 'Beta')"
db.Execute "INSERT INTO T2 VALUES ('A1', 3.14), ('B2', 2.71)"
' 刪表
db.Execute "DROP TABLE T1"
db.Execute "DROP TABLE T2"
Set db = Nothing
End Sub
執行 TestSetup 後再執行 RecoverDeletedTables,即可看到 T1、T2 被恢復並保留資料。
SELECT INTO 會失敗,需先手動處理RunSQL 可能逾時,建議分批或用 DAO.Recordset 迴圈寫入將模組封裝為 .accda 增益集,即可在任意 Access 專案中載入使用,無需重複匯入代碼。