I'm a big fan of Microsoft Access. Detractors will say that it's not a "real" database, and some claim that it shouldn't be used in business environments. I disagree. Access has its limitations, but it's also got some great benefits that can help small businesses (or even small departments in large businesses) manage their data with minimal investments of time and money.
Developing a Microsoft Access application is less time-consuming that building a full-blown application in C# or Java, or some other language. What's more, it's a beginner-friendly database tool that doesn't require an enormous learning curve. That allows small businesses to develop applications that help them manage their data, without a huge investment in IT. I often see Access used as the next step up from an Excel-based database, and its a great path forward.
It does have its downsides, however, and one of them is database corruption. Corruption can occur with any database tool, but it happens more frequently with Access. These corruptions can cause data loss, not to mention lost productivity when the database is down. To mitigate these concerns, there are steps that you can take to reduce the likelihood of corruption, and reduce recovery time when it occurs.
Symptoms
How will you know when you've got a database corruption?
There are several error messages that can crop up, with the most common being this:
My image is a little blurry. Sorry about that. I'm using an old image here, since I don't happen to have any corrupt databases lying around.
The message tells you that the database is in an inconsistent state. This is the symptom I see most often with a corrupt database, though it is certainly not the only one.
Some other common messages are:
"The database cannot be opened because the VBA project contained in it cannot be read"
"1 errors occurred when you loaded the form or report"
"Error accessing file. Network connection may have been lost"
A more subtle symptom is when you've got a feature in your Access database that has always worked fine, and has suddenly stopped working. Usually when this happens, its because you've got some bad data, but it can happen when there is corruption as well. This one is very hard to diagnose, and I've had some very knowledgeable Access developers scratch their heads for a long time on this. You'll probably need an Access guru to figure this one out.
Causes
The causes of Access corruption are well-documented, so I won't try to cover all of the details here. A quick google will yield a lot of information. Instead, I'm going to tell you what I've observed.
From my experience. Access tends to work very reliably and without corruption when you have a single user using the database on a single computer, and the database is stored locally on that computer's hard drive.
That's pretty limiting, though. Most often, you'll need your database shared by multiple users, which means putting it on a shared drive. This is where I'll often see corruptions become an issue.
A typical scenario goes like this: someone has a database that's working great on their local computer. They copy it to a shared network drive (either cloud or internal), and start sharing it with other users. It works great for a while, but then experiences a corruption. Once that is resolved, users often find that the corruption recurs frequently. I recently had a client who'd had a trouble-free database for years, but then it suddenly was getting corrupted two or three times a week. Another client had a database that went corrupt several time a day!
So what's happening here? It seems to me that Access doesn't handle network interfacing particularly well. Once you're asking it to pass data back and forth across a network connection, that's when you start to have problems. Not always, of course. But much more frequently. Don't despair, though. Using Access on a network can be a viable solution, but you'll need to take some steps to prevent corruption. Keep reading...
Recovery
Before I tell you how to prevent this situation, I'll offer a couple of suggestions for recovering from a corrupt database.
First, and I can't stress this enough, ALWAYS make sure you're database is being backed up. I recommend you set up a process to back up your database at least once a day. In a worst-case scenario, you may need to restore your database from a backup.
Before you resort to restoring your database, though, you should try using the Compact and Repair option. It's on the Database Tools tab on the ribbon, and it'll walk you through the process. Most of the time, this fixes the problem.
Another technique involves rebuilding your database, and importing as many of the objects as possible. Here's how that would work:
Create a new Microsoft Access database, with nothing in it.
Import the tables. If you get errors while importing, try importing one table at a time. Sometimes I've found that its just a single table that is corrupt, and all others will import. In that scenario, you can consider restoring the single corrupt table from a backup.
Import the queries, forms, reports, macros and modules. As above, if you get errors, try importing just one object at a time, or just a handful at a time. I've seen cases where there is a single form that won't import. It was a pain in the neck to figure out which one, but I was able to recover everything else from the live copy of the database. I then copied the one corrupt form from a backup copy, and we were back in business.
You may need to reset some of the database properties, like the start-up form. Pull up File -- Options, and then click "Current database" on the left pane. Set all options on this page as appropriate.
As a last resort, you may need to restore a copy of the database from backup. In that case, you're likely to lose some data, of course, but losing a day's worth of data is better than losing an entire database. Here's an important warning, though: sometimes database corruption isn't obvious immediately. Users sometimes ignore errors for a couple of days before they tell you, meaning the file that was backed up last night or the night before might also be corrupt. If your database is only updated once a week or once a month, this problem is compounded. Carefully consider how many back-copies of the database you need to keep.
Prevention
The good news is that there are a few simple things you can do to minimize the chance of corruption. Once again, you can google this topic and get many suggestions, but I'll add my two cents here. In dealing with this many times over the years, these are the tactics I routinely take to minimize corruptions.
First, I use a split database design with my Access databases. I described this scenario in a recent blog post, which you can catch up on here. Keeping your forms, reports and queries on the local computer, and linking them to data on your shared server minimizes the parts of the app that Access has to shuttle across the network, and this seems to help.
Second, I make sure users are aware that they should close the app when they are not using it. Some people tend to open the app first thing in the morning, and leave it running all day. Worse, some of them leave it running overnight. I've got one client where its common to leave an app running for an entire week, shutting down the computer only on the weekend. I think this is a bad idea for many reasons, one of which is that it increases the likelihood of corruption in Access. I've noticed that the longer a database is kept open, the greater the likelihood of corruption. When users get in the habit of closing the app when they are not using it, the incidents of corruptions decreases significantly.
Along a similar line, I also make sure that every application I develop has an obvious method of closing, usually in the form of a "Close Database" button on the menu. This is preferable to shutting the app down with task manager, for example, because all objects in the database will be properly closed. In addition that "Close Database" button serves as a reminder that I'd like them to close it while its not in use.
Finally, I'm conscientious of properly closing all objects and handling all errors in my VBA code. Failure to do so leads to memory leaks, and also seems to increase the possibility of corruptions. I use the "set x = nothing" statement to remove all object variables from memory; I close any forms or reports that I've opened; and I use "On Error Goto" statements liberally. All of these techniques make the application more user-friendly and less likely to experience issues, including corruption.
Need help?
Do you suspect that you've got a corruption in your Access database, but aren't able to resolve it yourself? Set up a Zoom call with me, and I'll take a look. Having done this many times, I can usually spot the problem quickly and put you back on track.
One final thought: did you know its possible to convert your Access table to SQL server, while still keeping your forms, queries and reports in Access? True story! In a previous blog post, I talked about linking Access to SQL Server. In my next Access blog, I'll go over the process of converting your tables to SQL Server. To be notified when that post goes live, subscribe using the form below.
Although Access corruptions can be frustrating and time-consuming, I firmly believe that they are worth the trouble. For small businesses without a huge IT budget, they are a cost-effective method of getting a handle on your data. Taking the few precautions I've mentioned here will help to ensure that your application stays up and humming along.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions #learningneverstops #microsoftaccess #msaccess #database #dataanalysis #smallbusiness
© 2023 North Port Solutions, LLC. All right reserved.
Comments