How to recover missing objects from a Microsoft Access database
Jul 19, 2019
When a Microsoft Access database becomes corrupted, some tables, forms, reports or Visual Basic modules may disappear and become completely inaccesible. Nevertheless, they could still be recoverable.
These tables, forms, reports or Visual Basic modules, are disconnected from the database, but still "floating" somewhere in the file.
I call them orphan objects . This article is about how to recover them.
What is an orphan object?
It is a piece of isolated data that in the past was the core of a table, form, report or Visual Basic module but now is detached from the database structure. Orphan objects are the remains of a previous state.
Cases in which orphan objects are generated:
A table, form, report, or Visual Basic module has been deleted.
Although the object is no longer in the database's internal index, it may still be recoverable for a while.
An object has been damaged and has become inaccessible, with or without error.
Although the object data is inconsistent with the datatabase internal structure and though inaccessible, remains of the object may still be in the file.
An Access accdb or mdb file is partially destroyed and the index data has been lost.
Without the database's internal index, it is difficult to get the objects back, even using a data recovery tool.
An Access database keeps an internal index with all the objects that we can see in the navigation pane plus some others that form part of the system, which are kept hidden.
Each entry in this index contains the name of the object plus a reference to its data. The object's data is stored separately in different areas of the accdb or mdb file.
Warning! orphaned objects are fragile
They reside in areas of the file marked as available that could be filled in with new data at any time. If this happens, any objects in the affected space will be destroyed.
Even if you don't add or edit anything, orphaned objects can be lost. For example, during the execution of a query, Microsoft Access could create temporary tables that could replace and destroy some orphan objects.
If your table, form, or Visual Basic code has become inaccessible, it is recommended that you make a copy of the file as soon as possible to preserve any orphaned data.
Compact and Repair?
From the point of view of Microsoft Access, and the tool Compact and Repair, orphaned objects do not exist.
For Access, orphan objects are free space and thus they are completely invisible to it.
Recovering orphan objects
Let's see how orphaned objects can be recovered with the tool AccessFIX. For this test I have chosen a corrupt file whose internal index has been completely destroyed. I have taken it from the project How I repaired 28 corrupt databases.
Cannot open the database
When trying to open the accdb file, Access returns this curious error message, which apparently is not relatated to the problem.
The accdb file is not opened and Access goes to the initial screen.
Recovering with AccessFIX
Although the database index doesn't even exist, AccessFIX is able to recover tables, forms, reports and modules.
No queries or macros are recovered.
All tables are recovered, but there is no way to know their names without the database index. The user can only recognize a table by its contents: for example, Recovered table 14 is the Customers table.
Forms and Reports
Forms and reports are also recovered but, as with tables, names are lost. From its content we know, for example, that Recovered form 7 is the Customer Details form.
All the modules and also the Visual Basic associated with forms and reports are recovered. In the case of Visual Basic, AccessFIX can take the names from the module's own data despite the missing index.
In forms and reports with Visual Basic we won't know the name of the object itself, but we will know the name of its associated Visual Basic. In these cases, AccessFIX creates an empty form or report with the original name to which to save the Visual Basic. The user will later need to manually move this Visual Basic to its corresponding unnamed form.
Orphan tables without a definition
It is possible that an orphan table could also have lost its definition.
When this happens, the AccessFIX preview will display a warning triangle icon close to the table. Text fields will be directly recoverable, but other fields can only be recovered by importing the table's definition from an external database.
An orphan table without a definition looks like this in AccessFIX:
We call orphaned objects those Visual Basic tables, forms, reports or modules that become disconnected from the main database structure but which are still recoverable.
If you've tried other data recovery tools and they haven't found anything, try AccessFIX before giving up, as there could be orphaned objects in your file.
There is more information about Access data recovery and repair of damaged databases in our blog ACCDB Crash Help.