-
4
- #1
I found this very useful code sample posted else were and thought others might find it helpful.
I did *not* write this.
I used the code listed in the article in an Access 2000 DB and it worked perfect.
The same technique is also outline in the Microsoft knowledge base article # KB179161. The KB uses different code, but the same technique.
Original article found at URL:
In case the URL ever fails here is a quote of the article.
'====================
Quote begins here.
'====================
November 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
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 **********************************
'====================
Quote ends here.
'====================
I did *not* write this.
I used the code listed in the article in an Access 2000 DB and it worked perfect.
The same technique is also outline in the Microsoft knowledge base article # KB179161. The KB uses different code, but the same technique.
Original article found at URL:
In case the URL ever fails here is a quote of the article.
'====================
Quote begins here.
'====================
November 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
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 **********************************
'====================
Quote ends here.
'====================
