How to Recover a Table Deleted from an Access Database

So you accidentally deleted a table, huh?

Have you ever deleted an Access table … and then suddenly felt that pain in the pit of your stomach? Well, be of good cheer because all may not be lost. There are at least 2 ways to retrieve your deleted table– one very simple, and another that requires code. But, you must act quickly because there are limits to what may be recovered … and when.

Good Old-Fashioned Ctl-Z

Before we launch off on a complicated DAO solution, it’s always worth the effort to try a simple Ctl-Z undo command. This process will only work if you haven’t performed some other Cut, Copy Paste Undo combination of actions before trying to restore the table. All the same, there’s no extra charge for attempting the Undo, so give it a whirl. If that fails, then you need to write code.

VBA Code-Based Solution

The code below may be copied and pasted into an Access Module and executed from the Immediate Window by executing this line of code

    UndeleteTable("MyTable")

As I stated in the introduction, there are some limits as to what may be restored … and when. Here’s the exception list:

• The database has not been closed since the deletion of the table
• The database has not been compacted since the deletion of the table
• The table was deleted using the Microsoft Access user interface

Ok, with all that in mind, here is the code. Copy the code below and put it in your MS Access toolbox for that rainy day when you pull a boneheaded stunt and delete some precious table that you haven’t had the presence of mind to backup.

' ***************  BEGIN CODE HERE  *********************************
' This code was previously posted at Comp.Databases.MS-Access by a
' kind soul some years ago.  I forgot to record that person's name so
' I am unable to give credit where it is due :-(
' 
' It seems to have been adapted from the Knowledge Base Article  Q179161.
'
' This module contains simple Visual Basic for Applications function
' that you can use to recover a table deleted from a Microsoft Access
' for Windows 95 and Microsoft Access 97 database under the following
' conditions:
'
' - The database has not been closed since the deletion of the table.
' - The database has not been compacted since the deletion of the table.
' - The table was deleted using the Microsoft Access user interface.
'
' NOTE: If multiple tables have inadvertently been deleted, this function
' recovers only the last table that was deleted. The other tables are lost.


Function UnDeleteTable(Optional sName As String)

Dim db As DAO.DATABASE
Dim tdf As DAO.TableDef
Dim sTable As String
Dim sSQL As String
Dim sMsg As String

    If IsMissing(sName) Then sName = "RestoredTable"
    If Len(sName) = 0 Then sName = "RestoredTable"
    
    Set db = CurrentDb()

    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) = "~tmp" Then
           sTable = tdf.Name
           sSQL = "SELECT [" & sTable & "].* INTO " & sName
           sSQL = sSQL & " FROM [" & sTable & "];"
           
           db.Execute sSQL
           
           sMsg = "A deleted table has been restored as " & sName
           MsgBox sMsg, vbOKOnly, "Restored"
           GoTo Exit_Undelete
        End If
    Next
    
    ' If the code has fallen to this point, then no deleted
    ' tables exist in the catalog and none are recoverable.
    MsgBox "No Recoverable Tables Found", vbOKOnly, "Not Found"

Exit_Undelete:
     Set db = Nothing
     Exit Function
     
Err_Undelete:
     MsgBox Err.Description
     Resume Exit_Undelete

End Function
' ****************  END CODE HERE  **********************************

See All Articles by Columnist Danny Lesandrini