I Hope I Broke The Code

My intention is not to break VBA code. It is to break the "code" of how to keep Access from crashing in the development process.

One of the most sickening things in life is when you think you have a handle on your problems, only to have Reality pop up its ugly head and show you that you were wrong. The problem still exists. You were only kidding yourself.

Doing application development with Access 2003 requires you to break the code. The code is all the nonsense you need to do in order to go beyond the most basic aspects of Access.

There are many things about Access that get in the way of generating a polished application. If you care to "correct" design funnies (not ha-ha) in Access, you have to write a lot of VBA code. A considerable number of settings in Access will cause you grief. You have the added tension of making sure all of those settings are corrected.

People get upset when their favorite sports team gets picked on by referees. Your team seems to be playing by the rules but is being penalized anyway.

You spend years figuring out best practices only to find that Access rudely plays by different rules and destroys hours of work. So a developer adds to logical rules of best practices, a stupid list of "must-do's" to keep the product from crashing.

The successful Access developer knows just how to shake the chicken bones over the PC.

I have collected a list of counter-intuitive steps that one must take to keep the badness away. It seems to me that if anybody at Microsoft cared about Access developers, they would -- at best, fix bugs; at least, build in the chicken bones.

It seems ridiculous to me that in order to escape the evil "Microsoft is sorry" message, you must take time to save early and often, use a non-Microsoft compactor, use a hyper-awkward /decompile feature, and every so often move all objects to a new .mdb file.

How could Microsoft take on some of this nonsense?

1. They could have a much more intelligent backup command. You need to save multiple copies, not just one.

2. The built-in compact/repair command is absolutely brain-dead and stops you in your tracks with an error message that reflects poor design on their part -- not your part.

3. Why isn't decompile built into Access. Sure, you can start Access from the command line with a /decompile switch. This isn't DOS. Windows doesn't make that an easy thing to do.

4. Microsoft cheerfully tells you that if you get in trouble, merely copy your objects to a new .mdb. OK, let's say you have a corrupted form. It will not copy across and will not tell you that it did not. Sure, you can count objects. You have to count because it doesn't give you a total number of objects. Too hard, I guess. If an object did not make it across, then it's defective. Now what? They tell you to "simply" get one from a backup. Yeah, sure. As if. If you use Access to select all available objects and copy them from old to new, you really don't have a complete copy. What about database relationships? What about references? What about database properties?

With computers I expect some sort of automation. Yes, of course, I can manually take care of all these details in a tedious process of making sure everything is copied. But if Microsoft provided tools to do this for us, we wouldn't have to invent the tools over and over again.

I have written these tools that should have been built in:

1. Archiver - copies the .mdb to a compressed folder that is stamped with the filename, date and time.

2. Compactor - uses a single command to compact and repair the database. The built-in compact/repair fails much too often. My program never fails.

3. Decompile - this is a tricky context menu item (another 1-line command) that uses Access to decompile the VBA in a given .mdb. It would be nice to have this as a built-in command that would then recompile the code. But no.

4. Rebuild - This involves copying all objects, one at a time, from old .mdb to new .mdb. Then the references and properties are copied across. This consists of about 50 lines of VBA code. If any object fails to copy, you are informed. This copy process works (at least for me) much better than SaveAsText, LoadFromText. (Notice the unparallel naming convention?) LoadFromText can render your target database useless. It's quite frightening unless you use the built-in "backup". Yeah, right.

...

In going through my notes today about what can cause problems in an Access .mdb, I found that unused code can be a problem. Of course, there is no way that I know of, other than brute force, to determine what code has been orphaned.

I also found that it's a no-no to copy a control to the clipboard and then paste it back into a form or report. I guess I'll have to take my chance on that one. If I can't do that, then I'm really crippled.

...

Book of the day: "Fixing Access Annoyances" (O'Reilly).

...

Quote of the day: "Good grief." -- Charlie Brown