This article is about to create a simple VBA function used to recover deleted table from a Microsoft Access. Whenever we delete the table from database window, the table isn't actually deleted although it appears to be. The table is recoverable until that particular database is open. We can recover the table by running the undo function. This article shows that how we can recover tables deleted. The recoverability is possible only when the access database is not close, access database is not compacted since the table deleted and table is deleted using user interface.
For implementation of this utility we have to create a table as shown in Fig 1.1.
Now we have to write function for recoverability. Now we have to test this function. For that we have to delete the table from Access database. After deleting the table we have to call the undo function from the immediate window of visual basic editor as shown in Fig 1.2.
As we call this function the message will prompt for recover the deleted table as shown in Fig 1.3.
As we press on restored button the deleted table will recover as shown in Fig 1.4.
Option Compare Database Public Function undo() Dim strTablename As String, i As Integer, StrSqlString As String For i = 0 To CurrentDb.TableDefs.Count - 1 If Left(CurrentDb.TableDefs(i).Name, 4) = "~tmp" Then strTablename = CurrentDb.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" End If Next i Exit Function End Function
It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. ERP Makers will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.