IT disasters do not necessarily occur due to fire, flood, or any other cataclysmic event. Sometimes, a simple mistake or error of judgment such as faulty maintenance or backup or inadvertent application usage may land an Access administrator in a huge problem. I have been always apprehensive about using complex or nested queries on my Access database and there is a strong reason why I have avoided doing that. Whenever we are using complex queries on the Access database, there is always a problem!
Actually, the role of complex or nested queries is to fetch data from other queries which may further hit others. In the process, the Access database starts writing unnecessary queries, resulting in piling up of temporary data. Essentially, the Access database user is not aware of such data pile.
Oftentimes, even after working on a small amount of data the query performs slowly due to its complex nature, and this puts stress on the JET engine. In this case, the slowdown of the process of fetching the data through queries is the piled-up temporary data.
Further, during this process, if Access chokes, then there is no way to avoid corruption in the backend file.
To avoid Access corruption, caused due to data accumulation, all Access users with administrative roles were intimated by email to follow a few preventive measures such as:
- Avoid using complex queries on the database, which may impede the database performance due to data accumulation and finally leads to database corruption.
- Split the database wherein the backend data consists of Tables which is not accessed by the users directly, and the frontend data comprises queries and other Access functions.
- Maintain backup copy of the entire database.
- Keep writing-off part of the temporary data to the temporary tables. This speeds up the query mostly by a factor of 10 or sometimes more, however, it fails to provide a permanent solution.
- Install Power Query feature for Access database where users created a dynamic connection with Excel workbook and this connection was continuously refreshed to get the updates from the database.
- Schedule Compact and Repair utility as soon as the database is shut down. Automatic ‘compact on close’ is done to reduce the redundant spaces regularly from the database.
Note: Users with the administrative role are assigned “read-write-delete” functions in the Access database. An administrative role can be assigned to multiple users, for example, the heads of different departments.
But, when one of the administrative users forgot to follow the above 5 stated rules, our organization’s Access database got corrupt
Root Cause Analysis (RCA) of corruption in Access Database issue
Ours is not a big organization, hence an Access database is big enough to store data. These Access databases are categorized on the basis of different departments for example ‘Database for Finance’ is different from ‘Database for Marketing’ and all the databases are made available on a common physical server.
However, one of the users with administrative rights forgot about that mail and started writing complex queries. These complex queries started creating unnecessary temporary files at the backend and one fine day the data that had piled up over a period of time resulted in corruption in the Access database. All the activities, including database accessibility, related to that database came to an abrupt end.
Even after aligning the Access database and taking all the preventive measures, a small error committed unknowingly by an administrative user, led to a big problem.
Now that the corruption had occurred, our first job was to resolve the corruption error and make the database live again.
Resolution methods adopted to repair Access database
RCA helped us in identifying the cause of the issue and the resolution method.
Restore through backup: We had a ready backup of the entire database available for database restoration. The following steps were performed to restore the backup:
- Opened File Explorer and browsed to select a healthy copy of the database
- Copied the database to the location where the corrupted database needed to be replaced. There was an option to replace the existing database and we selected that option.
- Opened the database to verify if the database was accessible.
To our dismay, the backup copy did not seem to be healthy. And, we realized that the Access database available on Excel hadn’t been refreshed for long.
That’s when the real problem started.
Our Access database was not accessible, the backup was not healthy, the Excel workbook with Power Query was not refreshed, and as we had been already running Compact and Repair utility, there was no chance of Access database recovery from the inbuilt utility.
The ultimate solution for database repair
The inaccessible database was creating havoc amongst users. Most users were left stranded and were not able to perform the routine task. We really had to act fast and resolve this issue asap. Now the best way to resolve was to repair the corrupt database with third-party software that could recover the entire database without prolonging the downtime.
We searched for an efficient Access database recovery software and out of the few available options, decided to choose Stellar Repair for Access. We read the reviews posted on different sites and thought of trying the demo version.
Note: As a precautionary measure, we had taken a backup copy of the database.
It turned out to be a DIY software. Once we submitted the corrupt Access file, the software provided the preview of the entire database for the final check. Also, the Stellar support team was more than helpful in resolving our queries.
It was a moment of sheer delight. We got the software activated, repaired, and saved the entire Access database within no time. The corruption issue was resolved fully and once again all the users could access the database.
Conclusion
There are various instances when the Access database can become inaccessible, and a major problem with this database is that it is prone to corruption.
Due to this reason I always take care not to create complex queries. Such queries are known to result in major issues such as creating unnecessary temporary files at the backend, slowing down the process of fetching data, ultimately leading to corruption in the Access database. If this happens, it needs immediate attention.
Recently, I came across one of the major findings as conducted by quest. It was clearly stated that hardware failure is the major cause of business impact, reaching a level of 75% (check the table below for reference). Such hardware or software failures have a direct business impact and for that reason, they must be attended to with top priority.
Though database backup provides the instant solution things go haywire when the backup is not healthy. Third-party software like Stellar Repair for Access is the best option when it comes to repairing the corrupt Access database.
In our case, where the Access database got corrupt due to complex queries the software provided instant results. A major benefit of the software is that it can be tested for its performance without activation. And we could save our data immediately after activation. There was no time lag and we could resolve corruption errors by restoring the database components into an entirely new database.
Users could access the Access database and we were relieved!
- Windows 10 Laptop not recognizing headphones? Here how to fix it
- Solved: Windows 10 100 disk usage after Update
- Windows 10 start menu not opening after update? here how to fix it
- Fix the NVIDIA Installer Failed issue on Windows 10