How to recover a MS Access table that has lost its definition
Jul 19, 2019
When a Microsoft Access database becomes corrupt and one of its tables loses its definition, all of its records will also be lost. The only way to recover them is to create a new definition or import a compatible one from another database.
It will be difficult or impossible to continue working with a database when the accdb or mdb file becomes corrupt. In the worst cases, the database can't even be opened. If it can be opened, you are likely to be faced with a variety of error messages such as inaccessible or missing objects or blank records.
Depending on the case, we can solve the problem using Compact and Repair, using a recovery tool, or using other methods such as import everything into a new database.
However, if the problem is related to a table definition being lost, the possibilities will be drastically reduced.
The definition is the data structure that stores all the details that can be edited in a Microsoft Access database from the design view of a table.
Each table has a definition with all the information about its fields (name, type and attributes) and its indexes.
Loss of a table's definition can also occur in severely damaged Access files, such as truncated files, which can only be repaired using a data recovery utility. Most tools will not be able to recover any information from a table if it has lost its definition.
How to recognize a table with a damaged definition
It is imposible to open a table that has lost its definition. If we try to open the table or its design view we will get this error:
This error message does not necessarily mean that the table definition has been lost. It is, however, true the other way around and we'll see this error if we lose the table definition..
If the file cannot be opened, you can identify a table with no definition using the preview of AccessFIX. Other tools aren't suitable for the task as with most utilities the table will appear empty or will not be in the results at all.
How the error is caused internally
I'm going to explain briefly how tables are stored internally and what the problem are in these cases.
A table is stored in three different zones of the binary content of the file:
Name. It is stored within the database index of objects, separated from the rest of the table data.
Definition. The definition contains all the details about the table and corresponds to the data edited from the table's design view such as field names and data types, and also indexes.
Records. They are stored throughout the entire database file. If a part of the accdb or mdb file is damaged, only the records in that part will be lost, but the remaining records will still be available.
-
If the database's index of objects is damaged, we will be able to recover an unnamed table.
-
If some records are damaged, we will be able to recover the remaining records.
- However, if the definition is lost, we will lose the whole table, even if all the record data is still "there". This is because to be able to interpret any row of a table, the definition is required to decode the data. Currently, the only way I know to recover this data is using AccessFIX, which can recover all of the records of the table partially or completely. I'll talk about this, in detail, later.
What if we repair with Compact and Repair?
Don't do it. If you don't have a backup copy, you will definitely lose the affected tables. Compact and Repair Database "solves" the problem by completely removing these damaged tables... without giving any warning.
Let's make a test with a file that has lost the definition from one of its tables, named Customers. Notice the screenshots that follow and how the table disappears after applying Compact and Repair.
To do the following tests I have used the corrupt-customers-def.accdb file from the project How I repaired 28 corrupt databases.
Before
The definition of the Customers table is corrupted. If we try to open the table, we get an error.
After
After applying Compact and Repair the table Customers not only disappears, but also all its data is deleted from the file.
Recovering with AccessFIX
Let's recover the same file from the previous example with AccessFIX. Look at the screenshots below:
This was the table before it was damaged
The Customers table looked like this in Access.
Now it can't even be opened
After the failure, any attempt to access the table causes an error.
Recovering with AccessFIX
The tool recovers the table with its 29 records, but shows us a warning triangle in the table to indicate that there is a problem.
AccessFIX is able to generate a new definition, but with limitations: 1. The original field names are lost. 2. Only text fields are recovered.
In the next section, I explain a possible solution to this.
Improving the recovery
The results can be greatly improved if we have an old copy of the database. It doesn't matter that it doesn't have records, or that it is from years ago, as long as it contains an identical definition of the same table.
AccessFIX has an option to use a definition from an external file. The number of recovered records will be the same but it will now have all their details.
Let's see what the process would be like:
We select the external file
From AccessFIX, activate the Advanced recovery and select the damaged file. Then, in the structure replacement option, we select the old copy. The copy provide the missing definition.
Improved Recovery
Things have improved a lot now. The yellow warning symbol no longer appears, field names are recovered, and all fields including non-text fields such as the ID field, are also recovered.
The recovered table in Access
Once we have saved the file from AccessFIX, we have completely recovered the table.
Summary
Corruption of a Microsoft Access file can result in different database failures. One of the most critical of which occurs when a table loses its definition. That makes it very difficult to recover its records.
The tool AccessFIX with the default options can, in these cases, directly recover the text fields of all of the records. Additionally, we will be able to fully recover these tables if we use an old copy of the database, even if it is empty, just by applying the AccessFIX Structure replacement feature during recovery.
There is more useful information about Access data recovery in our blog ACCDB Crash Help.
