Ever accidentally deleted one of your Microsoft Access tables?...And had no backup?
There is a way to recover it if you act before deleting another table or before closing the database. Microsoft Access temporarily stores the table with a prefix of "~tmp", but it's hidden from view.
By running the function below, you can insert all the records of the deleted table into a new table.
This code was taken from the Knowledgebase. Be sure to add the Microsoft DAO Object library to your references if you are running Access 2000 or later.
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
If you now call Undo function from the Immediate window after having deleted a table, you'll now see a new table created, named MyUndeletedTable.
Code courtesy of MDBMAKERS.com
See the following article on - How do I use the Immediate window?
Why not check out the following for much more information on using VBA in Microsoft Access: