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
Monday, March 26, 2007
Saturday, March 24, 2007
Hope
After beating my brains out over Access crashing/burning without letting me save my design work, I tried a very general Usenet group search (but specifically for Microsoft Access) for the dreaded "Microsoft is sorry" error message. I was able to verify that all Windows XP and Access 2003 and Jet 4.0 are all up to date and that none of the PC-specific suggestions were causing my problems. It kept coming back to the suggestion that my database file (.mdb) [actually it's everything but the database] probably has cancer lurking within.
I've spent time writing programs to compact/repair outside of Access, and to copy all objects, references, and properties into a new .mdb. But it seems something was missing. It appears that I need to decompile when things start going funny. (Not ha-ha funny.) I had added a shortcut that would let me right-click on an .mdb file and chose decompile to decompile that .mdb, but it stopped working when I upgraded to Access 2003. I rewrote that right-click command from scratch and it worked!
Now I have these features available as menu items when I right-click on an .mdb file in Windows Explorer:
Decompile
Compact and Repair*
Archive
If those won't make the .mdb crash less often, then I run another program which copies everything to a new .mdb and I begin again.
The development process now involves saving whatever I'm working on several times a minute. Every time I make enough changes that I'd hate to do them again, I exit out and compact and repair then archive.
This nonsense has let me get back to rapid development.
Many thanks to Geoff Hollander and Steve Titus of PAUG for listening to my problems and not replying, "It must have been something you did." Thanks also the the World Wide Web and books on Access annoyances.
= )
*Yes, I know there is a built-in menu item under Tools to do this. Unfortunately, it does such a poor job, I use my program, which is essentially a one-line command supported by quite a bit of code to set things up.
I've spent time writing programs to compact/repair outside of Access, and to copy all objects, references, and properties into a new .mdb. But it seems something was missing. It appears that I need to decompile when things start going funny. (Not ha-ha funny.) I had added a shortcut that would let me right-click on an .mdb file and chose decompile to decompile that .mdb, but it stopped working when I upgraded to Access 2003. I rewrote that right-click command from scratch and it worked!
Now I have these features available as menu items when I right-click on an .mdb file in Windows Explorer:
Decompile
Compact and Repair*
Archive
If those won't make the .mdb crash less often, then I run another program which copies everything to a new .mdb and I begin again.
The development process now involves saving whatever I'm working on several times a minute. Every time I make enough changes that I'd hate to do them again, I exit out and compact and repair then archive.
This nonsense has let me get back to rapid development.
Many thanks to Geoff Hollander and Steve Titus of PAUG for listening to my problems and not replying, "It must have been something you did." Thanks also the the World Wide Web and books on Access annoyances.
= )
*Yes, I know there is a built-in menu item under Tools to do this. Unfortunately, it does such a poor job, I use my program, which is essentially a one-line command supported by quite a bit of code to set things up.
Monday, March 19, 2007
Fellow Curmudgeons
Joy: http://www.crankygeeks.com/
I am not alone. John C. Dvorak is my favorite writer. When I get a PC Magazine, I go to his articles first. He is a refreshing change!
I am not alone. John C. Dvorak is my favorite writer. When I get a PC Magazine, I go to his articles first. He is a refreshing change!
Re: "Outlook = Look Out!"
Re: Outlook = Look Out http://duanepdx.blogspot.com/2007/03/outlook-look-out.html
What would happen if you wrote an application program that was so good that it would give the user everything they needed. When you released new versions the users would not be interested in paying again (as they have in the past, again, and again, and again...). Most of these users have automatic software updates and so you might sneak in a "security update" that breaks the product and recommends that you jump back into the pay and pay again cycle. Just a thought.
Is Windows XP good enough that people with think twice before buying a new computer to run Vista? Will "security updates" sabotage XP installations? Just wondering...
What would happen if you wrote an application program that was so good that it would give the user everything they needed. When you released new versions the users would not be interested in paying again (as they have in the past, again, and again, and again...). Most of these users have automatic software updates and so you might sneak in a "security update" that breaks the product and recommends that you jump back into the pay and pay again cycle. Just a thought.
Is Windows XP good enough that people with think twice before buying a new computer to run Vista? Will "security updates" sabotage XP installations? Just wondering...
Thursday, March 15, 2007
Outlook = "Look Out!"
Ever put your trust in Outlook to do as it's told and send emails when you click the SEND button? Sometimes it doesn't work. You might find out when you shut down your computer that "there are messages in your outbox". It asks if you are sure you want to shut down but you really have no choice because before you can respond, Windows shuts down.
When Outlook won't send messages, I've snooped around looking at the task manager and have seen two instances of Outlook running. Only one is visible on the screen or in the taskbar. I suppose each is waiting for the other to act in a responsible way. So I bought a $5 shareware add-in program called Outlook Shutdown. If you shut down Outlook, it shuts down it's hidden cousins also.
When I'm going to be away from my computer for awhile, I use ZoneAlarm's Internet Lock to keep vandals away. If Outlook is running, it will never ever be able to send or receive an email again. It's that stupid! I have to shut it down and then reload it.
On Monday something very suspicious happened. Outlook on two computers got a "Microsoft is sorry" screen and I kept clicking until I got to what appears to be a heavy-handed attempt by Microsoft to get you to upgrade:
When Outlook won't send messages, I've snooped around looking at the task manager and have seen two instances of Outlook running. Only one is visible on the screen or in the taskbar. I suppose each is waiting for the other to act in a responsible way. So I bought a $5 shareware add-in program called Outlook Shutdown. If you shut down Outlook, it shuts down it's hidden cousins also.
When I'm going to be away from my computer for awhile, I use ZoneAlarm's Internet Lock to keep vandals away. If Outlook is running, it will never ever be able to send or receive an email again. It's that stupid! I have to shut it down and then reload it.
On Monday something very suspicious happened. Outlook on two computers got a "Microsoft is sorry" screen and I kept clicking until I got to what appears to be a heavy-handed attempt by Microsoft to get you to upgrade:
Monday, March 12, 2007
RAD = Rapid Application Development
RAD, man!
Rapid Application Develpment means you can fly from idea to reality. Yeah, right.
I spend entirely too much of my development day being harassed by Microsoft products that are incredibly STUPID. A person with an IQ of 40 would not have to be told the same thing over and over and over...
Windows consistently gets in the way and offers up defaults that are bone-headed and wrong. It's not bad the first time you correct it, but it doesn't remember the correction even one time. How about a little artificial intelligence rather that an Aero interface?
The web has been terrific in translating error messages into actions to be taken to fix the problem. But when the error message is " has encountered a problem and needs to close" is so general that it could mean anything, you can't get any relief. What "has encountered a problem and needs to close" means is this: poor software design.
Google for that phrase and you will find hits for these products: Windows printer spooler, Internet Explorer, Windows Media Player, Microsoft Messenger, Microsoft Front Page, Access (!), Microsoft Word, and more. The "solutions" are dubious. For Access, the theme is "you have a corrupted database... simply do this..." Yeah, right! I do NOT have a corrupted database and to do what is suggested would compromise my work.
Lately, a well-crafted database file running under Access produces the error just by loading it and then waiting for a while. Just when you are enjoying your experience (XP), the horrid message comes up. "Microsoft is sorry." By the way, "XP" used to stand for Extreme Programming before Microsoft bastardized the name.
Imagine taking a road trip. You get several miles down the road and a light comes on on the dash that says, "Sorry, but you need to go back home and start again."
Good grief.
Rapid Application Develpment means you can fly from idea to reality. Yeah, right.
I spend entirely too much of my development day being harassed by Microsoft products that are incredibly STUPID. A person with an IQ of 40 would not have to be told the same thing over and over and over...
Windows consistently gets in the way and offers up defaults that are bone-headed and wrong. It's not bad the first time you correct it, but it doesn't remember the correction even one time. How about a little artificial intelligence rather that an Aero interface?
The web has been terrific in translating error messages into actions to be taken to fix the problem. But when the error message is "
Google for that phrase and you will find hits for these products: Windows printer spooler, Internet Explorer, Windows Media Player, Microsoft Messenger, Microsoft Front Page, Access (!), Microsoft Word, and more. The "solutions" are dubious. For Access, the theme is "you have a corrupted database... simply do this..." Yeah, right! I do NOT have a corrupted database and to do what is suggested would compromise my work.
Lately, a well-crafted database file running under Access produces the error just by loading it and then waiting for a while. Just when you are enjoying your experience (XP), the horrid message comes up. "Microsoft is sorry." By the way, "XP" used to stand for Extreme Programming before Microsoft bastardized the name.
Imagine taking a road trip. You get several miles down the road and a light comes on on the dash that says, "Sorry, but you need to go back home and start again."
Good grief.
Saturday, March 10, 2007
Follow Us -- The Wow Begins Now
In a recent version of Forbes magazine, Steven Manes, reports on Vista and Office 2007. It had me laughing and crying at the same time.
http://members.forbes.com/global/2007/0312/038.html
http://members.forbes.com/global/2007/0312/038a.html
Fer instance:
Vista
"Vista is at best mildly annoying and at worst makes you want to rush to Redmond, Wash. and rip somebody's liver out."
"If I can find plenty of problems in a matter of hours, why can't Microsoft? Most likely answer: It did -- and it doesn't care."
"As usual, things Microsoft was touting last time have mysteriously gone away in favor of putative new wonders."
""As Bill Gates winds down his roles at Microsoft, Windows Vista may be the chief software architect's swan song. It's a shame his legacy is something so utterly unimaginative, internally discordant and woefully out of tune."
Office 2K7
"Users: Adapt or die. Or stick with older versions that do most of what this one does and work the way they taught you to."
(Remarks about how functionality has been lost to flash and nonsense.)
Well said!
http://members.forbes.com/global/2007/0312/038.html
http://members.forbes.com/global/2007/0312/038a.html
Fer instance:
Vista
"Vista is at best mildly annoying and at worst makes you want to rush to Redmond, Wash. and rip somebody's liver out."
"If I can find plenty of problems in a matter of hours, why can't Microsoft? Most likely answer: It did -- and it doesn't care."
"As usual, things Microsoft was touting last time have mysteriously gone away in favor of putative new wonders."
""As Bill Gates winds down his roles at Microsoft, Windows Vista may be the chief software architect's swan song. It's a shame his legacy is something so utterly unimaginative, internally discordant and woefully out of tune."
Office 2K7
"Users: Adapt or die. Or stick with older versions that do most of what this one does and work the way they taught you to."
(Remarks about how functionality has been lost to flash and nonsense.)
Well said!
Monday, March 5, 2007
Friday, March 2, 2007
Think You're Pretty Good At Using Photoshop?
http://www.flickr.com/photos/mattijn/sets/311250/
Click "view as slideshow".
Interview: http://flickrz.fotografisch.at/topics/mattijn/main
All of his work is done with a digital camera and Photoshop 7. His Photoshop skills are first-rate, but they pale to his imagination and creativity. Bravo to Mattijn, a Flickr friend.
Click "view as slideshow".
Interview: http://flickrz.fotografisch.at/topics/mattijn/main
All of his work is done with a digital camera and Photoshop 7. His Photoshop skills are first-rate, but they pale to his imagination and creativity. Bravo to Mattijn, a Flickr friend.
The Unspeakable Law
"The Unspeakable Law"
(one of Murphy's Laws)
If it's good, it goes away.
If it's bad, it happens.
After being so impressed with USB2 and how a USB2 socket is analogous to a power socket -- just plug in and it works.
I've got lots of USB2 sockets. Today I found that none of them worked. I didn't realize that all of them were broken because my mouse was working. Oops. It's got a USB-PS2 adaptor.
After trying the usual tricks, I came up in Safe Mode and looked at the Device Manager for USB. It showed nothing wrong. But, then again, it showed a lot of duplication. So I threw caution to the wind and deleted duplicates. When I rebooted, everything was beautiful again.
In troubleshooting, crawling around on the floor, I noticed that the USB cables are mostly nondescript black. So I decided to use my Dymo label maker and label the plugs. Of course, the first label came out on my photo printer, even though that isn't an option of the Dymo program. I turned off the photo printer and tried again. No label. I had a choice of printers: Dymo and Dymo (copy). I tried the other one and it worked!
When you go to the bother to label cables, sometimes you make some decisions about what gets plugged in where. For some reason, I was one socket short on the back side of my PC. So I added a USB2 hub for the slower stuff. Now I have 6 free sockets on the front of my PC and plugged in behind are cables for:
1. PDA
2. Graphics Tablet
3. X-Keys programmable keyboard
4. X-10 remote control computer
5. 250 Gb Archive external drive
6. 250 Gb Music external drive
7. An open case with power supply and USB-to-IDE converter for old drives
8. Scanner
9. Photo printer
10. Dymo label maker printer
The thumb drives and my camera flash card reader plug into the front.
I just looked at my framed Data General ad from the 70's. It shows an upraised fist and the caption is "Power To The Programmer". Amen.
(one of Murphy's Laws)
If it's good, it goes away.
If it's bad, it happens.
After being so impressed with USB2 and how a USB2 socket is analogous to a power socket -- just plug in and it works.
I've got lots of USB2 sockets. Today I found that none of them worked. I didn't realize that all of them were broken because my mouse was working. Oops. It's got a USB-PS2 adaptor.
After trying the usual tricks, I came up in Safe Mode and looked at the Device Manager for USB. It showed nothing wrong. But, then again, it showed a lot of duplication. So I threw caution to the wind and deleted duplicates. When I rebooted, everything was beautiful again.
In troubleshooting, crawling around on the floor, I noticed that the USB cables are mostly nondescript black. So I decided to use my Dymo label maker and label the plugs. Of course, the first label came out on my photo printer, even though that isn't an option of the Dymo program. I turned off the photo printer and tried again. No label. I had a choice of printers: Dymo and Dymo (copy). I tried the other one and it worked!
When you go to the bother to label cables, sometimes you make some decisions about what gets plugged in where. For some reason, I was one socket short on the back side of my PC. So I added a USB2 hub for the slower stuff. Now I have 6 free sockets on the front of my PC and plugged in behind are cables for:
1. PDA
2. Graphics Tablet
3. X-Keys programmable keyboard
4. X-10 remote control computer
5. 250 Gb Archive external drive
6. 250 Gb Music external drive
7. An open case with power supply and USB-to-IDE converter for old drives
8. Scanner
9. Photo printer
10. Dymo label maker printer
The thumb drives and my camera flash card reader plug into the front.
I just looked at my framed Data General ad from the 70's. It shows an upraised fist and the caption is "Power To The Programmer". Amen.
Thursday, March 1, 2007
Access Curmudgeon
I work on software design by playing around with the product as it develops. Sometimes I discover that I need to make a lot of changes to improve it. Or I'll have an inspiration that causes me to rip out what is working so I can replace it with something better. It's an iterative process. Polishing, removing blemishes.
It's satisfying, at least theoretically. But imagine that you are trying to concentrate on your design when an obnoxious person comes up and bangs on your keyboard or unplugs the computer. It's annoying and it's hard to get back where you were before the insult.
Microsoft Access 2003 is like that annoying person. Just when things are going well -- too well, it pulls the plug. You lose all the work you've done since the last backup. You get a sick feeling when Access stops responding for too long. That inevitable "Microsoft is sorry" message is soon to appear.
I've noticed lately that when the sorry message appears, you are more screwed than you might think at first. The .mdb file size increases and that increase is some sort of cancer that makes the next crash more likely. The larger the file becomes, the more frequent the crashes. Microsoft recommends that you simply copy all objects from the old .mdb to a new one. OK, where then is the command to copy references and database properties? Ha, ha. You can easily loose a lot of work if you're not paranoid and obsessively careful.
Microsoft is known for its testing and its focus groups, right? What kind of testing is done and who are these people in the groups? Working with this software is like working with not just a stupid assistant, but one with a mean streak.
If your product has problems, do you fix them? Or do you ignore them because you are certain that your new whiz-bang products will replace the old ones.
I have an advantage of those with less experience using Access. I know a lot of work-arounds. Some of them that used to work, no longer do. Also, I don't expect the product to work perfectly. Far from it!
Back to "work".
It's satisfying, at least theoretically. But imagine that you are trying to concentrate on your design when an obnoxious person comes up and bangs on your keyboard or unplugs the computer. It's annoying and it's hard to get back where you were before the insult.
Microsoft Access 2003 is like that annoying person. Just when things are going well -- too well, it pulls the plug. You lose all the work you've done since the last backup. You get a sick feeling when Access stops responding for too long. That inevitable "Microsoft is sorry" message is soon to appear.
I've noticed lately that when the sorry message appears, you are more screwed than you might think at first. The .mdb file size increases and that increase is some sort of cancer that makes the next crash more likely. The larger the file becomes, the more frequent the crashes. Microsoft recommends that you simply copy all objects from the old .mdb to a new one. OK, where then is the command to copy references and database properties? Ha, ha. You can easily loose a lot of work if you're not paranoid and obsessively careful.
Microsoft is known for its testing and its focus groups, right? What kind of testing is done and who are these people in the groups? Working with this software is like working with not just a stupid assistant, but one with a mean streak.
If your product has problems, do you fix them? Or do you ignore them because you are certain that your new whiz-bang products will replace the old ones.
I have an advantage of those with less experience using Access. I know a lot of work-arounds. Some of them that used to work, no longer do. Also, I don't expect the product to work perfectly. Far from it!
Back to "work".
Subscribe to:
Posts (Atom)