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
» 
Comment and Contribute
