Data Management

10 ways to prevent Access database corruption

Nothing frustrates Access users like a corrupted database. With a little know-how, you might get lucky enough to repair the database or at least recover the data. Sometimes, a third-party product that specializes in data recovery can help. But you could end up rebuilding and re-entering data. (Or rather, your replacement could.) A more proactive approach is to avoid corruption in the first place. Here are some strategies for preventing your databases from becoming corrupted.
By Susan Sales Harkins and Gustav Brock

Nothing frustrates the full spectrum of Access users -- from casual user to developer -- quite like a corrupted database. With a little know-how, you might get lucky enough to repair the database or at least recover the data. Sometimes, a third-party product that specializes in recovering data from a corrupted database can help. But you could end up rebuilding and re-entering data. (Or rather, your replacement will rebuild and re-enter it.)

A more proactive approach to the situation is to avoid corruption in the first place. Here are some strategies for preventing your databases from becoming corrupted.

Note: This information is also available as a PDF download.

#1: Split your database

If more than one person will access the database, split it into two pieces: a backend that stores the data in tables and a front end that contains everything else (forms and reports). Access has a wizard that holds your hand through the process so there's no excuse not to split a shared database. Name the two ends appropriately. Remember, the backend contains your "gold" -- your data. With the data and interface objects in separate databases, you can easily replace the front end from a backup, if necessary. In addition, this setup makes enhancements easier to incorporate into your system.

There is some discussion about whether the front end should be stored on a network server (and shared) or on a local drive. This decision really has no impact on corruption, so the choice is yours. If possible, consider write-protecting a shared front end. If the worst happens and something destroys the front end, simply obtain a fresh copy of it to replace the broken one and reboot the local system.

#2: Store temporary tables in a backend

If a database creates, populates, and then deletes temporary tables, keep those tables in a separate backend database to prevent bloat. Name the additional backend accordingly, making its purpose obvious. This additional backend can be shared or local.

#3: Don't use memo fields

Avoid using memo fields if possible. They often, indirectly, cause corruption. Even though the database in these cases often can be repaired, some content in the memo fields might be lost. If you need memo fields, keep them in separate tables and create a one-to-one relationship to the parent table(s). Even better, move memo tables to a separate backend database file and name the file accordingly, to indicate its purpose.

#4: Don't store picture files

Usually, you shouldn't store picture files in a database. If you must, treat them the same way you would a memo field (see #3). Access has no problem attaching tables from multiple backend databases.

#5: Create temporary tables to speed up queries

If you run complex or nested queries (where one query pulls data from others that hit still others), Access may write a lot of temporary data that you never see. Most often, this happens when a query that works with a small amount of data performs slowly, putting stress on the JET engine. If Access chokes during this process, you can end up with a corrupt backend file.

To prevent this problem, write some of the temporary data to temporary tables. There's no universal method to recommend. Analyze the specifics and run some tests to find the best solution. However, sometimes the use of just one temporary table can minimize the chance of corruption and speed up the queries by a factor of 10 or more.

#6: Be careful with wireless networks (WiFi)

A connection may work fine, but multiple users or powerful neighborhood networks (or other noise sources) can abruptly cut off the connection. That can corrupt the database file if you are writing to it at the time. This type of interference isn't a problem if users are mostly reading from the database.

#7: Be careful with WAN connections

A WAN connection that covers any connection from a local system to a server via the Internet can cause trouble. Reading the database may be slow but acceptable. However, writing to the database is error prone and can cause corruption. When bottlenecked traffic interrupts data transfer, Access times out, believing the connection has been lost. This behavior usually leaves the backend database in a corrupted state.

#8: Don't put Mac and Windows users on the same network

If Mac and Windows users share the same network and experience problems, establish a separate network for database users. Macs are extremely noisy, and typical Mac applications generate vast amounts of network traffic when moving large graphics files and printing. In a shared environment, use only high-quality network components.

It's best to keep the database traffic separate from the graphics traffic, as much as possible. You can accomplish this by allowing administrative workstations to connect directly to the server with the shared database via a local switch.

#9: Troubleshoot network hardware

If corruption just happens from time to time, you may have to deal with a network hardware issue. First, try to narrow down the workstation and swap the error-prone station with another. If the issue follows the workstation, you know that's the source. It might be easiest to get rid of the workstation.

If the problem isn't specific to the workstation, the error is most likely to be found in the connection from the workplace to the network switch -- including the actual switch port. If the cable's in good shape, swap the ports between the workstations. If the error source now swaps too, replace the switch; if not replace the cable. If cables aren't permanently installed or don't cross from floor to floor, you can try replacing them before swapping workstations.

#10: Check the server's configuration

Sometimes, the configuration for the server where you've stored the shared backend database file is the culprit. You'll need a specialist to track down and resolve this type of problem. There are several possibilities, from server parameters to a malfunctioning disc controller to a misconfigured RAID array.

You can temporarily move the backend file to a different location, like one of the popular NAS devices or a shared folder on a workstation. If the problem with corruption goes away, call the specialist. If not, the corruption's source is somewhere else (#1 through #9).


Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the publications director for Database Advisors. You can reach her at ssharkins@gmail.com.

Gustav Brock is a consultant and developer at Cactus Data ApS, an independent software house specializing in accounting and time billing systems and custom database applications. He has been working with Microsoft Access since version 1.0 and is a frequent contributor to the forum of developers hosted by Database Advisors.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

91 comments
robertsmike
robertsmike

Corruption in access databases is a common problem and may arise due to many reasons like virus attack, database header file corruption, hardware issue etc. Whatever be the reason of the corruption, all data become inaccessible to users. Found some cases and their solution to fix the corruption in access database files: http://accessdatabaserepairsoftware.blogspot.com/


Hope it will be useful !

Awahili Guni
Awahili Guni

Actually the author is correct but should had put a little more details or depth in aspect as why the two and two shouldn't be together. In a way yes they can and in another way no they cannot. Just like one cannot run on a PCI with IDE and RAID together (one has to have one or the other) or it will corrupt itself. With MAC - there is a clash when IDE is use and will create it to become noisy; not so much when RAID is used. This would pertain to those who are running on older Servers or various BIOS (yes, there are some "bad BIOS" out there) in spite of all the configurations one has done. Is there a work-around for this? So far I have run-in to many forums all over, seen many so-called "answers" but the responses from others, I would have to imply that the majority of them were complains over those who responded that it worked. So which is better IDE or RAID? It actually really depends on your BIOS and how your Server and Computer (both Win and Mac) are functioning; not so much as the Access Program itself. In some cases, while it is unfortunate, there are no resolutions and something has to give - so in a way, I do partially side by that statement of MACS being noisy; but in another way, there should had been a little more in depth or detail explanation as why in my humble opinion. While I am solely Windows, not anti-MAC at all - but I have come across such issues of this sort. Might that be the answer to your question?

david.chapman
david.chapman

If you have more than ten screens in your application a sure way to cause corruption is to copy and paste a screen. You might think it worked, saved you a lot of time and is great to obtain a uniform feel, but next time you you start the database that screen will not work, unexpected results will occur and it will get worse and worse. Be very wary of temporary tables in a backend of a shared database. More than one user might create the same table. I prefer them on the user's PC with the application, have never blamed them for corruption.

jw
jw

As a power user, I was responsible for two Access databases. One suffered corruption problems, the other I devised a web (asp) front end. It never got corrupted (an unexpected bonus) - I suspect because it only ever saw one user i.e. the web server on the same box. Somebody more techie might comment whether my theory is right?

tonyaaa17
tonyaaa17

In a prior life, a reliable access app suddenly started corrupting weekly. It turned out that new computers had been purchased, and they were set to go to sleep after a period of time. Having a shared MS Access application open when the PC goes to sleep is a recipe for disaster, if the data is contained in the Access DB. - Tony

tonyaaa17
tonyaaa17

I develop in several environments, and find that I can quickly develop a nice looking front end in MS Access. What I have learned (due to the corruption issue) is to not store the DATA in MS Access - I create a SQL db for the tables, and link them to MS Access (possibly with some temp tables in access as needed). Sometimes I need to develop pass-thru queries to force the query processing to occur in SQL rather than in access. That way, if the access "database" (really application) becomes corrupt, you can restore an earlier copy without data loss. And, I've not had the application version get corrupted since I started using this methodology. There are several free SQL servers that can be used (MS SQL Express, MySQL) as well as the big dog SQL servers. - Tony

lrnstevens
lrnstevens

And when all that fails you can always turn to specialized data recovery software. I use AccessFix and I'm very happy with the results. You can check it out at www.cimaware.com/main/products/accessfix.php

susan_harkins
susan_harkins

I had to start a new thread--that previous one wouldn't take any new responses, so I hope you see this. This is also a second post in response, but the other one has never shown up. I don't know whether I sent it into a black hole or just goofed and didn't publish it at all. I like a good healthy discussion, even when folks disagree. (Okay, I really want to be right all the time... .) Seriously, I learn from these conversations. As long as we're treating each other with respect, I'm fine with disagreements. Like you, I agree that this is an open forum and discussions often take on a life of their own. Having said that, I know that many "big dogs" have little (no) respect for Access or developers who use it -- not all of the big dogs, but some of them. We've seen some of that in response to the article. I think it's out of place in a professional discussion. I am not talking to you personally dawgit -- just in general. But, I've got thick skin and it is an open forum. Anyone who wants to trash Access should do so, just don't expect those of us who use it to say, "Oh... isn't that sweet..." ;) BTW, I have a fan club? Who knew? Oh... more importantly, who's collecting the dues???? ;)

cactus
cactus

Fact is that Access stands behind thousands and thousands of applications "out there" - from tiny wipped-during-lunch-break apps to extensive mission critical ERP apps. Thus, to claim that Access is not a real database only proves lack of knowledge. These apps have to be maintained and nursed, and if you know how, it is not more difficult than so many other tasks taken care of by an IT pro. Again, claiming that it is not possible or reliable to run a shared Access database on a normal well-tuned network, only proves lack of skills and knowledge. Further, to compare a shared database as Access (or rather JET) to a standard SQL server engine makes no sense. Two different animals. In many cases a server based engine is indeed preferable but simple things as backup, server resources or portability may be prohibitive and in many cases pure overkill. This is an interesting discussion on its own but is beyond the scope of this article. /gustav

cactus
cactus

As you may know, to write such 10 statements in limited space, some corners have to be cut. But the remarks on the noisy Macs are based on experience. We have several advertising agencies and TV/film production companies among our clients all of them running an Access based time/billing/ERP application, and the list of "issues" we have seen is endless. Main problem is that Mac tech people (not users, they don't know anything) believe they understand networking. As a general rule and warning (exceptions exist of course) they don't. Worse, when (not if) something misbehaves regarding the network, they blame _everything else_ connected to the network for being the source of trouble. One example. Recently at a client we saw a corrupted backend once a week. Normally it was perhaps once a year. Nothing at our end was changed so the cause had to be something else. The network was one of those that had been expanded in 57 steps; it was close to impossible to track down why things were set up as they were. Local switches were all over and users in one end printed to a big RIP and printer across the network. So we cut through all this, isolated the pc users on a separate LAN with just an uplink to the mothership network to gain Internet access. From day one: No corruption. Now the client moved. A brand new network was installed and guess what: Corruption again. We found out that the network was not configured as to our recommendations, and again to cut short, we isolated the pc users and their server and printer on their own switch and trouble went away. The reason for the trouble with the Macs is not the Macs by themselves but how the users punish these machines. Typically they run 24 hours all week long loaded with all kind of applications that download all sorts of data. Users run heavy application that saves and copy multi gigabyte files between workstations, servers, RIPs, local FTP servers etc. etc. Our favourite is when they attach 100 MB files to e-mails wondering why this brings their Exchange to a crawl. "The mail is down!" they claim when the e-mail client times out, but the answer is "Wait and see." It is possible to make Macs and their users to coexist with pc users and Access but it requires a top-notch network which Mac people typically neither do or will understand not to say are willing to allocate the money for. It is more fun to spend money on a 30" flat screen or a new stack of RAID disks for a NAS. /gustav

BALTHOR
BALTHOR

Your engine would never kill on the expressway because the vehicle is being operated as per the engineering design.None of these computer problems would ever exist if computers were being operated as per design."We got her done all right,but it will never work".

gregads
gregads

For what it's worth, my setup is this as follows (2-5 users, typically) 1. Backend, containing tables and relationships only, located on a SMB network share 2. Front end development database containing all queries and modules. 3. To distribute the front end to the users, I create a MDE file from the front-end development database. Users copy that file locally (this works better for us than having the users run from the shared location). This allows me some modicum of version control, as I date-stamp the MDEs in the startup screen. There's still some major issues with scalability when using an MSAccess backend, as we all know. However, using a MDE frontend and PostgreSQL / MySQL backend is a good transitional step towards a true enterprise database application for small companies like ours with limited IT resources who need to deploy a simple forms/reports oriented app quickly.

mail.dave
mail.dave

Hello. I appreciate your article and much of it's content. But when you talk about Macs and being "noisy", you are flaunting ignorant and antiquated, platform-bias in what it supposed to be a document about Access databases. In informal terms: Are you freekin kidding me ? Let me laugh you out the room, and tell you never to come back. What rank amateur nonsense !! "Don???t put Mac and Windows users on the same network" "Macs are extremely noisy" - what the hell ? If you were working for me that kind of tech-voodoo disinformation would be a good way to get fired. Are you talking about 12 years ago and AppleTalk ? "Oh noz... it's t3h Mac ! Don't let it get on our networkz !!" This is so pathetic. Noisy ? If a computer is transferring files, then yes, SUPRISE, there will be lots of tcp/ip traffic. What the ... ? Look, if you need to deal with broadcast traffic, handle that via policy. Staring with NetBios !! *roll of the eyes* and including Rendezvous/Bonjour/ZeroConf if you don't want that traffic on your network. Stop it at network level management. And tell me what any of this has to do with Access Databases ? You might as well say, "don't put any Windows PCs on the network because they are a potential source of network flooding due to bot exploits"

byrne1
byrne1

Of course, there is another way to avoid Access DB corruption and that is to avoid using it and use something else like Firebird or Postgresql

susan_harkins
susan_harkins

I'm really interested in hearing your Access corruption horror stories -- and how you resolved them.

Editor's Picks