Wednesday, January 1, 2020

Singing (and Dancing) With Access

Dancing with the Stars, Singing with the Idols

I like to think I can make MS Access sing and dance. Give me a task and most of the time I'll find a way to accomplish it. Once in a while it takes longer to navigate through a new kind of process, but so far I've never failed (well there was that once when I couldn't mimic Excel exactly in a datasheet view in a form, but it was on the clients "it would be nice..." list anyway). However, there are limits to what even Access can do. When I find myself stumped, I tend to want to blame Access. But that's just an excuse, on several levels. It shames me when I resort to blaming someone else for my shortcomings. That makes me human, I guess. It doesn't make me right.

Que Será Será.

 Access is what it is. How trite and tautological, right? But one of the things that alternately frustrates and amuses me is that Microsoft designed Access to do exactly what it does, no more and no less. I refuse to admit defeat until I've exhausted all of the options. Sometimes I'm quite delighted to learn there is a way to do something I would have thought impossible. Often those tasks relate to working with URLs or other things webbie. It's something I know little about. However, I've learned never to say never, except when I've spent enough time Bingoogling to conclude I'm not going to find an answer. Then it's time for a long walk and maybe a fresh cuppa Costa Rican dark roast.

The corollary to that, of course, is that Access isn't what it isn't. VBA is a rich, powerful coding language that works under Windows on the desktop. It's not a web coding language. It never was, and despite all the Sturm und Drang about it over recent years, there's little chance it ever will be (I'd say zero chance, but, you know, never say never). You want to make web pages, go learn a web coding language. There are lots of them and you are smart enough to figure it out, if you must. Just don't waste my time and yours whining about something that nunca será.

 Mommas Don't Let Your Babies Grow Up to Be Cowboy Coders

I run into this from time to time on the on-line forums. The question comes in the form of "How do I write a function (or maybe some SQL) to do X, Y, and Z? And then, when the answer is along the lines of "Use the built-in Access feature that does X, Y and Z, the response comes back, "But I want to do it this way. How do I do that?"

One common example is form design. Bound forms are the heart and soul of nearly every Access Relational Database Application. Writing a lot of code to handle unbound forms seems like a real waste of time and resources in all but a few very specific situations. If you want to ride off into the sunset on a white mustang, be my guest. But don't lose sight of the fact that the end of the road for most cowboys includes a lot of isolation.

If you don't want to do it "the Access Way", that's your call, of course. But  don't complain when it turns out to be harder, less reliable and less flexible than "the Access Way".

Know Your Limitations

 Access, for example, doesn't do a great job with data security. It has other strengths. Keeping your data absolutely protected is not one of them. If that's a requirement for your Relational Database Application, find a different tool. Access is, and always be, my favored tool for creating Relational Database Applications. On the other hand, just like I wouldn't haul pigs to market in a Cadillac I am pretty sure there are better tools for a lot of tasks.

Access and I are going to be dancing partners for a long time to come, I hope.We just have to avoid stepping on each others toes.