Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Nov 20, 2000

How to Recover a Table Deleted from an Access Database

By Danny Lesandrini

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


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 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
    ' 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"

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

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

See All Articles by Columnist Danny Lesandrini

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM