Accidental deletion of Access table...can it be recovered 1

[SeeWard]

Hello,
There has been an incident where a table was deleted from Access 2003 .... the database was shut down so I can't retrieve it that way. Does any one know how one can recover a deleted table?
Tia

 

[PHV]

The usual way is to restore it from the last good backup.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SeeWard]

Yeah, I know ...I went to ask the person about that and it would seem there is no good back up for quite some time...they haven't been running nightly or weekly backups...

 

[GingerR]

Rule #1 broken again............

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[SeeWard]

Broken?? More like shattered....totally shattered...oh but NOW they will make sure to back things up....

 

[willir]

Rule number 1 is usually followed more diligently after a "personal expierience". Or the "school of hard knocks" is a very good teacher -- if you survive.

 

[Remou]

Hi
I found this at

http://www.databasejournal.com/features/msaccess/article.php/1494111,

but I do not think it will be of much use to you; it may be of use to someone else. I have not tried the code.

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.

'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 

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