Microsoft Access 2007 reliability issues and suggested workarounds

The following TechRepublic list was written for Access 2007. My impression is that Access 2003 is becoming less reliable over time, probably due to security fixes that aren’t fully tested on 2003. Even so, I think 2007 may have more corruption problems, though presumably Microsoft will eventually fix them.

10 ways to prevent Access database corruption | 10 Things | TechRepublic.com

#1: Split your database …

#3: Don’t use memo fields

Avoid using memo fields if possible. They often, indirectly, cause corruption….  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)…

#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) (and other less reliable network connections)

Access 2003 does not support views, I think they would help with #5.

The list of “risky” behaviors is an indictment of Microsoft’s software quality. Memo fields, for example, are very useful! I never used to see problems with nested queries in 2003, but I have recently.

My personal favorite is #1 on the list. I store data that isn’t going to be changed in a read-only file (set read-only file attribute).