Share via

How to: Recover Tables Deleted from a Database

  • Article

This topic shows how to create a sample Visual Basic for Applications (VBA) function that you can use to recover tables deleted from a Microsoft Access database under the following conditions:

  • The database has not been closed since the tables were deleted.

  • The database has not been compacted since the tables were deleted.

  • The tables were deleted using the Microsoft Access user interface.

  • The table does not contain any multivalue or Attachment fields.

Paste the following procedure into a standard module.

Sub RecoverDeletedTable() 
On Error GoTo ExitHere 
 
 Dim db As DAO.Database 
 Dim strTableName As String 
 Dim strSQL As String 
 Dim intCount As Integer 
 Dim blnRestored As Boolean 
 
 Set db = CurrentDb() 
 
 For intCount = 0 To db.TableDefs.Count - 1 
 strTableName = db.TableDefs(intCount).Name 
 If Left(strTableName, 4) = "~tmp" Then 
 strSQL = "SELECT DISTINCTROW [" & strTableName & "].* INTO " & Mid(strTableName, 5) & " FROM [" & strTableName & "];" 
 DoCmd.SetWarnings False 
 DoCmd.RunSQL strSQL 
 MsgBox "A deleted table has been restored, using the name '" & Mid(strTableName, 5) & "'", vbOKOnly, "Restored" 
 blnRestored = True 
 End If 
 Next intCount 
 
 If blnRestored = False Then 
MsgBox "No recoverable tables found", vbOKOnly 
 End If 
 
'*EXIT/ERROR* 
ExitHere: 
 DoCmd.SetWarnings True 
 Set db = Nothing 
 Exit Sub 
 
ErrorHandler: 
 MsgBox Err.Description 
 Resume ExitHere 
 
End Sub