databasedev.co.uk - database solutions and downloads for microsoft access

Recover Deleted Database Tables

How to Recover a Deleted Database Table

Ever accidentally deleted one of your Microsoft Access tables?...And had no backup?

There is a way to recover it if you act before deleting another table or before closing the database. Microsoft Access temporarily stores the table with a prefix of "~tmp", but it's hidden from view.

By running the function below, you can insert all the records of the deleted table into a new table.

This code was taken from the Knowledgebase. Be sure to add the Microsoft DAO Object library to your references if you are running Access 2000 or later.

Function Undo()

  Dim db As DAO.Database, strTablename As String
  Dim i As Integer, StrSqlString As String

  Set db = CurrentDb()

  For i = 0 To db.TableDefs.Count - 1

    If Left(db.TableDefs(i).Name, 4) = "~tmp" Then
       strTablename = db.TableDefs(i).Name
       StrSqlString = "SELECT DISTINCTROW [" & strTablename & _
         "].* INTO MyUndeletedTable FROM [" & strTablename & "];"
       DoCmd.SetWarnings False
       DoCmd.RunSQL StrSqlString
       DoCmd.SetWarnings True
       MsgBox "A table has been restored as MyUndeletedTable", _
         vbOKOnly, "Restored"
       GoTo Exit_Undo
    End If
  Next i
  MsgBox "No Recoverable Tables Found", vbOKOnly, "Not Found"

Exit_Undo:
     Set db = Nothing
     Exit Function
Err_Undo:
     MsgBox Err.Description
     Resume Exit_Undo

End Function

If you now call Undo function from the Immediate window after having deleted a table, you'll now see a new table created, named MyUndeletedTable.

Code courtesy of MDBMAKERS.com

See the following article on - How do I use the Immediate window?

Recommended Reading

Why not check out the following for much more information on using VBA in Microsoft Access: