The single worst defect of Microsoft Access

There's a lot to dislike about Microsoft Access. Even so, I use it intensively. It's a cross between a chainsaw and a swiss army knife with an unfortunate tendency to occasionally remove vital organs.

Powerful, versatile, dangerous.

When I use Microsoft Access I backup my data files very frequently and I set every linked Access data file I'm not actively editing to 'read only' status to protect it (Access table security model ranks high in its sins).

So, lots of bad news. But one insanity stands out above all the rest.

Access has a compulsion to break queries when you use linked files. This is one of those mult-ipart design problems:

1. The crummy security models and lousy organizational tools means that serious work requires lots of linkages to remote ODBC and Access data sources. (This ability to link across diverse data sources is actually a great power of Access, it extends to Sharepoint's SQL Server tables.)
2. Links to local sources use hard coded paths.
3. When a hard coded path changes, or a referenced table name changes, a trap has been set. The first time one opens a query referencing the link to the changed item, the query breaks. Joins vanish. Column names get odd prefixes. There's no warning and no protection.
   

There are some partial protections:

1. Use the drive letter substitution trick to reduce the hard coded path problem.
2. Wrap linked tables in a simple query that you can validate first, then have all other queries reference these "wrapper" queries. It's easy to fix the wrappers, and you don't break all the other queries.
   

If Access 2007 had fixed this behavior I'd be using it today, but since it's buggy and has design regressions I'm sticking with Access 2003.

There, I feel better now.