What Walks Like a Duck?

 

 I love MS Access. It's been very good to me. I’ve made a living using (primarily) Access for more than a dozen years. There’s one thing about it, though, that keeps me up at night. It’s too flexible.
 

You know the old saying, “If it walks like a duck and quacks like a duck, it’s a duck”. Well, that’s fine if you have a flock of ducks and you’re trying to pick out one chicken among them. Unfortunately, “an Access database” isn’t a Duck, or a Chicken. Or a Goose, Turkey, Partridge, Sage Grouse, Pheasant, Guinea fowl, Cornish hen or Dove. It’s all of those and much, much more.
 

My point? Oh, yeah. Sorry I got a bit hungry and went for a snack. I’m back and ready to state the point.

MS Access is a Relational Database Management System (RDBMS)*. Access is an interface design tool. Access is a Rapid Application Development (RAD) tool. Access is all of those and much, much more. It’s a Data Aggregation Tool; it’s an Extract, Transfer and Load (ETL) tool; it’s a prototyping tool. It’s even got secret sauce and a close, personal relationship (relational pun intended) with its big brother, SQL Server and its other brother, Excel. Oops, got carried away there. There’s no secret sauce.

MS Access has the ACE data engine. It’s a remarkable engine, the little engine that could. You can abuse it, confuse it, rearrange it and extend it. One thing for sure is that, if you can dream it, you can probably build it with ACE. The ACE data engine is almost too flexible and too easily manipulated. And that’s a big problem, from time to time, especially for novices.

Unlike Ray Kinsella in the Field of Dreams, Eli Whitney or Henry Ford, most new Access users come to the task with no background in the field, no experience designing and building a prototype, or a vision and burning passion to realize their dreams to make the world a better place through the power of their creation. They just have a job to do, and most often, little time to do it.

That means, unfortunately, they all too often don’t have an inclination to invest the time and effort into figuring out how to use the ACE data engine most effectively. It’s sort of like Ray Kinsella setting out to clear his corn field with a garden hoe and rake, or Henry Ford trying to build cars on an assembly line with no way to move the vehicles along except teams of horses hitched to each one. Possible? Sure. Practical? Not really.

While I do applaud the creative, inventive use of Access, I have to wonder if enough novices really grasp the significance of learning how to do it right. And by right, I mean the five Rules of Normalization. Actually, there are many, many discussions of normalization floating around on the internet. If you want to know more, just ask Bingoogle. They both know where to look.

I guess, to sum it up, I’m trying to say that all ducks are not born equal. Some are Chickens, some Geese, some Pheasants, and a few I’ve seen have been real Turkeys. Applying the rules of normalization is the best way to avoid those turkeys.

 

 

 

*I’ll get some argument on this point. Access ACE is not a full-fledged RDBMS and was never intended to be. I know, I’m glossing over that fact on my way to a larger point. Take it up with me in an email; I’ll be happy to hear your opinions on the issue. I'll probably share them too. Depends on how many nasty words you use--or don't use, if you get my meaning.

 

 

 

 

 

 

 

 

---

[1] I’ll get some argument on this point. Access ACE is not a full-fledged RDBMS and was never intended to be. I know, I’m glossing over that fact on my way to a larger point. Take it up with me in an email; I’ll be happy to hear your opinions on the issue.

Back to Earth for a Moment.

Recently, I’ve spent a lot of time thinking about, and working with, Access Web Apps, which is Microsoft’s tentative venture into making MS Access databases work “in the cloud”. I know, pretty sexy, huh? The Cloud: that’s the ticket. (Bow in the general direction of Jon Lovitz.)

 

Well, it’s not all that.

AWAs followed on from the idea that MS Access is the best small database solution available for Windows, and has been for over 20 years. So, moving it to the cloud was a no-brainer, at least in the beginning 6 years ago. Microsoft’s Access web database offerings turned out to be less than we hoped they would be, and still could be. We’re not done yet, so there’s still hope, but that hope has definitely faded with each passing month. However, that’s only part of my point. The rest of my thoughts have to do with how we think about, how we approach, what we do for, our clients. We’re Access developers. What we do is create Access database solutions, right? Well, not so fast.

 

While we may be tempted to think in terms of "a database solution", what we are really after, most of the time, is a "data management solution". Clients come looking for our help to solve problems within their organizations which usually, but not always, include managing some of their most important business-related data. They need to make processes more efficient, minimize transaction time and costs, and produce more actionable reports. Those are the things we must put front and center, not the specific tools we use to solve those problems. That means the specific tools we choose should not be the determining factor in how we do the job.

 

I'd say at least two other factors must be given higher priority than our choice of a specific tool:

 

Simplicity. A variation of Occam's Razor can be applied. When there are competing ways to get the job done, it's usually best to choose the simplest one. Not always, of course. There may be other complications which require more elaborate solutions. However, the end user's needs should be met with the least complex, but still effective, solution.

 

Cost. We must never lose sight of the fact that we, as developers, are expending SOMEONE's resources no matter what solution we implement. If a sexy solution offers us the chance to show off our chops, and it costs no more than a mundane solution that would get the same result, okay fine I say, show off. You don’t get that many chances to dance in front of an audience. Just remember, the person with the checkbook is paying for results, not a flamboyant Flamenco.

 

On the other hand, it's not likely to be in the client's best interest to invest two or three times the amount in a feel-good solution using the latest bleeding edge technology just because it can be done that way. Why go to the cloud? Is that what meets the minimum acceptable requirements of the project? If so, then do it. If not, resist the urge to try out something new, just so you can add it to your resume. I have yet to meet the client who told me, “Make sure you have a good time working on my project. I don’t really mind what it costs.”

 

I have had one client tell me, on the other hand, “If what you want to do will save me money, or make me more money, go ahead and do it.”

 

That’s been my motto for more than a dozen years now. I approach every new project with the same thing in mind. You should too.

Office 365 Plans For Access Web Apps

If you want to create an Access Web App, or AWA, you need two components:

 

1)      The MS Access Application, version 2013 or 2016

2)      An Office 365 Account which includes Access Services

 

Of course, if you work in a large organization which has deployed SharePoint 2013 or 2016 internally, you can use the Access Services provisioned on it (assuming that has been done). Many of us don't have that option. We can, however, get a cheap O365 account for our purposes. And that’s what I am going to talk about today.

 

Obviously, to create an ACCESS database or web app, you need MS Access. I think most people get that point without a lot of effort. Further, it’s pretty easy to understand that you need either the 2013 or 2016 version to support AWA’s. We’re used to versioning in software applications.  

 

It's the second component that gets murky, which Office 365 Account do you need and why?

 

Business Plans

Here’s the page for  Office 365 Business Plans. Microsoft’s website listing the basic plan options for Business. It used to be called “Small Business Plans”, it’s now just “Business Plans”, as opposed to "Enterprise Plans" or "Personal Plans".

Enterprise Plans

There’s also a page for Office 365 Enterprise Plans. I won’t go there today, but if you need the services and products offered in any of those plans, the higher costs for them are worthwhile.

 

Detailed Comparison of Plans

 Here's a page with detailed comparisons of the plans.

 

What Do You Get in an Office 365 Plan?

So, the first thing we see for Office 365 Business Plans is that two of the three plans include the “Standard” Office applications, which means Word, Excel, PowerPoint, Outlook and OneNote. None of them include the Access application itself. This will be the 2016 version of those applications; I believe you can opt for the 2013 version, but I also don’t understand why anyone would do that.

 

The absence of MS Access means, however, means you have to obtain a license for MS Access, the application, elsewhere. Regardless of which Business Plan you choose, you don’t get the Access Application.

 

Getting Access Services

 

The other component we’re looking for, Access Services, on the other hand, IS included in all three of these plans.

 

Good News! If you have a licensed copy of MS Access 2013 or MS Access 2016, all you need is the lowest cost plan Office 365 Business Essentials at $6.00 per month, or $5.00 per month if you buy an annual license.

 

This is important information for anyone looking to move their Access databases “into the cloud”. For $60.00 a year (annually) or $72.00 a year (monthly), you can have any number of Access Web Apps on the web. In my opinion, that is a very good deal.

Pass it on.

Thanks.

 

 

Where Do YOU Shop for Furniture?

The other day I was reading posts on www.UtterAccess and thinking about what kinds of questions people ask. It occurred to me that some people look for database design assistance as if they are Ikea shoppers, some like Ethan Allen shoppers, and some like Home Depot shoppers.

The Ikea shoppers always want a database template. They’re willing to do a minor amount of assembly, but they crave pre-packaged solutions that someone else put all the hard work into creating, packaging and delivering.

The Ethan Allen shoppers want a high-end, finished database that they can have delivered to their home or office. They’re not interested in learning much about it, only that it is delivered with a minimum of hassle on their part. They’re willing to pay the cost which goes with that product.

The Home Depot shoppers are looking for an associate in the warehouse store who can help them pick out materials, along with few tools, and maybe conduct a training session on Saturday morning, but they expect to do the hard work themselves. They know the final product is going to be a bit rough around the edges, but that’s a good trade-off for saving a lot of money.

One problem, of course, is when shoppers expect to pay Ikea or Home Depot prices for Ethan Allen products. That’s a bit frustrating, as a matter of fact. I can’t say that I really blame them, I suppose. If you could get a $3,000 sofa for $198, wouldn’t you take it?

Another problem is when shoppers want to pick up an Ikea bookcase that exactly fits that odd-shaped corner of their living room. Well, chances are high that anything you get from Ikea is going to have standard dimensions, aimed at 99% of their customers, and not at your odd-ball space. If you put your mind to it, one of those out-of-the-box bookshelves can be modified to work for you. You just need to be willing to put in the work to do it.

And the third problem is that Home Depot shoppers tend to run into frequent difficulties that keep them returning to the store for more parts, more tools and more tutorials. There’s a lot more hand-holding involved.

Actually, now that I think about it, none of these are really problems, with a capital P. They’re just different ways to think about what it means to be a support person in the wonderful world of online forums.

 

 

What Are People Looking For?

My website, www.gpcdata.com, has been active for years. I offer a lot of free, fully functional, sample Access databases and code examples. Recently, I was reviewing statistics on what people look for when finding me, and what they download when they get there.

 

As you'd guess, "Free Access Database" is a popular search term, usually accompanied with a specific version or category. Students seem to be popular. Lots of school staff and teachers out there looking for a way to track their classes or schools.

 

However, the most popular download, by a narrow margin, is "contacts". The version I have available is mostly aimed at tracking simple, client/contact related information, along with meetings and phone calls with those contacts.

 

Currently, there are two versions. The newest one is an accdb designed with Access 2013. It ought to run in Access 2010 and even 2007, although I haven't checked the latter recently. The other is the venerable mdb originally designed with Access 2003. I debated pulling the mdb out of the download, but after some recent conversations on www.UtterAccess.com, I decided to let it ride. Lots of people seem to be sticking with the older versions still, even Access 97 despite it's being two decades old.

 

In second place is an older version of my project and work tracking tool. I actually built it for my own use and have made many modifications to it over the years. The version I use every day is now modified to run under Access 2013/2016, with a custom Ribbon. It connects to SQL Azure tables now. That allows me to carry a copy of the Front End on my laptop and update work hours from any location without having to worry about resynching Access BEs.

 

Actually, there are three versions of Working Tracking, one for Access 2007, one for 2010 and newer, and one for 2003 (the mdb format). Interestingly enough, the 2007 version has proven slightly more popular over the years and still is in the most recent reporting period.

 

I'm mulling over the implications of this history of contacts and work tracking.

 

What do you think?