Recovering a deleted table

[jim_blandford]

I accidently deleted the only table containing all the data, and I cannot get it back, undo operation not available. I stupidly did not make a backup of the database.

The database is still quite big in size, is there any chance that the data is tied up in the form (I know it seems stupid because fields from in a form are bound from a table!)

Any ideas or help would be very much appreciated,

Jim.

 

[Jack Cowley]

If you haven't closed the database or compacted it there is a chance that you can recover the table. This article will tell you how to do it.

I hope its not too late but if it is you have learned a valuable lesson...

Jack

 

[jim_blandford]

Thank you for your response. Your help was too late...

I have learnt a valuable lesson indeed, and I am making regular back-ups of the database I am currently working on.

I am a complete novice and I was just testing this method of recovery, incase I ever needed it in the future. I followed the instructions however, when I clicked Compile database name, under the Debug menu the following error message was displayed.

"Compile error:

User-defined type not defined"

This is the code was it is on the website:

Function Undo()
Dim db As DAO.Database, strTablename As String
Dim i As Integer, StrSqlString As String
Set db = CurrentDb()
For i = 0 To db.TableDefs.Count - 1
If Left(db.TableDefs(i).Name, 4) = "~tmp" Then
strTablename = db.TableDefs(i).Name
StrSqlString = "SELECT DISTINCTROW [" & strTablename & _
"].* INTO MyUndeletedTable FROM [" & strTablename & "];"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSqlString
DoCmd.SetWarnings True
MsgBox "A table has been restored as MyUndeletedTable", _
vbOKOnly,"Restored"
GoTo Exit_Undo
End If
Next i
MsgBox "No Recoverable Tables Found", vbOKOnly, "Not Found"
Exit_Undo:
Set db = Nothing
Exit Function
Err_Undo:
MsgBox Err.Description
Resume Exit_Undo
End Function

The bit that is highlighted when the error message is displayed is

db As DAO.Database

How can I fix this?

It is not important that you reply and if you chose not to, then thats no problem, but I am just interested to see if this works.

Thanks, Jim.

 

[Jack Cowley]

Since you are getting that error I assume you are using Access2000 or greater. Access2000 changed from DAO code as its native code to ADO code and the code in the article is DAO. DAO code will work in Access2000 and up, but you need to select the DAO library for it to work. To do this open any code page where you see the Debug menu. Click on Tools > References. Scroll down until you find Microsoft DAO 3.6 Object Library and check it. Now compile your db and it should work.

If you still have a problem just let me know.

Good luck with your project!

Jack