Table corruption after applying append query in Access database

An append query in Microsoft Access adds records from a database table you are working in to another table. You can append records to a table in the current database that you are working in or into another Microsoft Access database. This feature of Access is quite useful when you want to add data to a different table where the data is based upon a selection criteria. However, append queries are not always the best way to add records to another database. There are possibilities of table getting corrupt and .mdb or .accdb file becoming inaccessible. In such situations, you would be required to compact and repair the database or restore the data from an updated backup. And, if nothing seems working, then you have to consider using an Access repair software.

Consider a scenario, wherein, you are running an append query, and receive the following error message:

“Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.”

Cause
The above error can be encountered if you have a table that has a self-join and Memo fields, and has referential integrity enforced. This may lead to table corruption.

(Referential Integrity refers to a concept which ensures that relationships between tables remain consistent.)

Resolution
In order to resolve this issue, you can consider the following Access database repair steps:

• Change the Memo fields to Text fields
• Move the Memo fields into a separate table, and then use a 1:1 relationship

While the above steps can fix the problem, you cannot address the same situation with large databases. And, if you use the Compact and Repair utility, it won't work as well. Therefore, you have to rely on third-party Access repair software. Such tools help you in repairing and restoring Access database along with its various components.