How to recover deleted tables and other objects from an MS Access database
Jul 19, 2019
How to recover tables, forms, reports, queries or Visual Basic code, that have been deleted from a Microsoft Access database.
When a table or any other object in a Microsoft Access database is deleted, two things happen internally:
- The corresponding entry in the internal index of objects is deleted.
- The space occupied by the object's data in the file is marked as available.
The object's data is not deleted directly nor is the space it was occuping recovered from the accdb or mdb file immediately. These unused areas of the file will be reused later when free space to store new data is needed, such as when creating a new form or adding new records to a table.
The space made available when deleting objects will only be recovered after running Compact and Repair, which will rewrite the whole database removing unused "gaps".
Deleted objects are fragile
It is advisable to make a backup as soon as possible to maximize the options available if a data recovery operation is needed.
Deleted objects reside in areas of the accdb or mdb file marked as available. If new data fills them in, the deleted object’s data will be destroyed.
A data recovery example
Example with a real accdb file of how to recover deleted objects
Access confirmation window before deleting.
To illustrate the recovery procedure we are going to delete all of the objects from a database containing tables, forms, reports, and Visual Basic.
Then, we are going to recover them using AccessFIX. Although AccessFIX can also recover records that have been deleted from a table, this article is not about that, but about situations where the table, or object of any type, is entirely deleted.
We delete everything
We select everything and delete tables, forms, reports, and Visual Basic modules from an Access database.
The database is empty
Although the database is completely empty, internally, many of the objects are still there in the form of orphan objects.
Deleted objects will remain in the file as long as we don't add new data or compact the file.
Recovering data with AccessFIX
AccessFIX can recover in this example 20/20 tables, 26/33 forms, 13/15 reports and 8/17 Visual Basic modules.
The number of recoverable objects is variable and depends on many factors such as whether any modifications have been made to the database after deletion.
Deleted and recovered tables
In this example, all the records from all the tables are recovered. The result is that good because the database was not modified after being deleted. If the database had continued being used, little by little, records would have been lost.
When a table is deleted, its name will always be lost: in the screenshot, Recovered table corresponds to the original table Customers.
Deleted and recovered forms and reports
Names are also lost in the case of forms and reports: in the example Recovered form 21 corresponds to the original form Customer Details.
Deleted and recovered modules and Visual Basic
2/6 Visual Basic modules have been recovered in this example. The result is less successful than with the other objects in spite of not having done any operation in the database after deletion. Maybe they were lost due to how the internal deletion procedure of Microsoft Access works, or maybe because the database was already compacted and thus had no free space available for new data.
On the positive side, we have been able to recover the VBA of 3/6 forms and 3/3 reports.
The names of the modules can be recovered thanks to the fact that the name can be extracted from the module's own data.
Additional information
Deleted objects are a particular case of orphan objects. If you need to check whether your database contains deleted objects, you may easily preview all the recoverable objects with AccessFIX.
To read more about different types of corruption in Access databases, see How I repaired 28 corrupt databases. There is more about general data recovery in our blog, ACCDB Crash Help.
