How to recover a deleted table of MS Access database using VBA code

Recover deleted table in MS Access database using VBA code

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.

Restore deleted table in MS Access using VBA. 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.

Restore deleted table in MS Access using VBA. Fig-1.2


As we call this function the message will prompt for recover the deleted table as shown in Fig 1.3.

Restore deleted table in MS Access using VBA. Fig-1.3


As we press on restored button the deleted table will recover as shown in Fig 1.4.

Restore deleted table in MS Access using VBA. Fig-1.4


VBA code

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. Access Guru 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.